getting proper date format from SQL search results (datetime.datetime)

7.2k views Asked by At

This is my first time using cx_oracle. Part of my project is to create a search engine that processes "SELECT FROM.." queries and displays them. This is for searching patient information and the tests he/she have been prescribed. I thought it would also be a good idea to return prescribe dates for tests.

Here is what I execute:

searchPatientName = "SELECT b.PATIENT_NO, a.NAME, c.TEST_NAME, b.TEST_DATE, b.RESULT FROM patient a, test_record b, test_type c WHERE b.TYPE_ID = c.TYPE_ID AND b.PATIENT_NO = a.HEALTH_CARE_NO AND a.NAME = :patient_name"

patientName = "Vera Fuselier"
search.execute(searchPatientName, patient_name=patientName)

rows = search.fetchall()                    
for row in rows:
    print(row)
break

The output I am getting is what I want for the project, but the date is not what I am wanting.

 (100103, 'Vera Fuselier', 'Blood Test', datetime.datetime(2012, 8, 30, 0, 0), 'Normal')
 (100103, 'Vera Fuselier', 'MRI', datetime.datetime(2014, 11, 30, 0, 0), 'Sclerosis')

Please notice how the date is not displaying the way I want to (with datetime.datetime), and I can't figure out a way to fix this.

I've been using this to no avail: getting date fields from oracle in correct format using Python and cx-oracle

How can I get the date to display in a format like: '12-08-30' for example?

1

There are 1 answers

2
muthan On

datetime is the date object of python, with that you could get any format you want in your case you can get your format with:

import datetime
row[3].strftime('%y-%m-%d')

Now for clarification of what is happening. With strftime() you can get a Date or Time String out of your Datetimeobject. with '%y-%m-%d' I formated it.

row[3] should be your dateobject element.

%y returns the Year with 2 digits like 14 for 2014. If you want the whole year you have to type %Y

%m returns the number of month and %d the number of day.

there are also options like weekday, the name of the month, etc.

you can read about that in the docs

So now your code should look like this:

import datetime

#your code to read the file

for row in rows:
    row[3] = row[3].strftime('%y-%m-%d')
    print(row)

#some other code