CODEWITHSUNDEEP
postgresqlrobotframework
user3303266
user3303266

Reputation: 371

SQL: Change the datetime to the exact string returned

See below for what is returned in my automated test for this query:

Select visit_date 
from patient_visits
where patient_id = '50' 
AND site_id = '216' 
ORDER by patient_id 
DESC LIMIT 1

08:52:48.406 DEBUG Executing : Select visit_date from patient_visits where patient_id = '50' AND site_id = '216' ORDER by patient_id DESC LIMIT 1 08:52:48.416 TRACE Return: [(datetime.date(2017, 2, 17),)]

When i run this in workbench i get

2017-02-17

How can i make the query return this instead of the datetime.date bit above. Some formatting needed?

Upvotes: 0

Views: 455

Answers (1)

Todor Minakov
Todor Minakov

Reputation: 20067

What you got from the database is python's datetime.date object - and that happens due to the db connector drivers casting the DB records to the their corresponding python counterparts. Trust me, it's much better this way than plain strings the user would have to parse and cast himself later.

Imaging the result of this query is stored in a variable ${record}, there are a couple of things to get to it, in the form you want.
First, the response is (pretty much always) a list of tuples; as in your case it will always be a single record, go for the 1st list member, and its first tuple member:

${the_date}=    Set Variable    ${record[0][0]}

Now {the_date} is the datetime.date object; there are at least two ways to get its string representation.

1) With strftime() (the pythonic way):

${the_date_string}=    Evaluate   $the_date.strftime('%Y-%m-%d')    datetime

here's a link for the strftime's directives

2) Using the fact it's a regular object, access its attributes and construct the result as you'd like:

${the_date_string}=    Set Variable  ${the_date.year}-${the_date.month}-${the_date.day}

Note that this ^ way, you'd most certainly loose the leading zeros in the month and day.

Upvotes: 0

Related Questions