How to convert datetime.datetime to datetime.date?

Question

From my sql query I'm getting output as datetime.datetime(2020, 9, 22, 0, 0)

query = '''SELECT checkin_date FROM `table1`
WHERE checkin_date BETWEEN %s AND %s'''

cursor.execute(query,(startDate, endDate)
results = cursor.fetchall()

#results:
#[(datetime.datetime(2020, 9, 22, 0, 0), datetime.datetime(2020, 9, 24, 0, 0))]

for res in results:
   ## When I print type I get correct result
   print(type(res[0]) ## <type 'datetime.datetime'>

   ##when I compare with another datetime.date (currentDate variable)
   if res[0] < currentDate:
   ## I get error `TypeError: can't compare datetime.datetime to datetime.date` *which is expected*

   ## But when I use .date()
   if res[0].date() < currentDate:
   ## I get `TypeError: can't compare datetime.date to unicode`

I tried converting currentDate to datetime.datetime, but still doesn't work. Can't seem to figure out what's the issue here.

The whole datetime and date dealio works differently in every make and model of database server. Please add a tag for mysql, postgresql, or whichever server you use. — O. Jones
– O. Jones, Commented Oct 22, 2020 at 12:13

O. Jones · Accepted Answer · 2020-10-22 12:31:49Z

1

To force your query to spit out the date format you want, change it to this:

     SELECT DATE_FORMAT(checkin_date, '%Y-%c-%d') 
       FROM table1
      WHERE DATE(checkin_date) BETWEEN %s AND %s

To make it able to use an index on your checkin_date column, change it to this.

     SELECT DATE_FORMAT(checkin_date, '%Y-%c-%d') 
       FROM table1
      WHERE checkin_date >= DATE(%s)
        AND checkin_date < DATE(%s) + INTERVAL 1 DAY

answered Oct 22, 2020 at 12:31

O. Jones

109k17 gold badges133 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Habib · Accepted Answer · 2020-10-22 12:43:29Z

1

Try this

splitting a datetime column into year, month and week

SELECT        Year(checkin_date), Month(Checkin_date), Day(Checkin_date), 
FORMAT(GETDATE(),'HH'), FORMAT(GETDATE(),'mm')
FROM            table1
WHERE        (CAST(checkin_date AS DATE) BETWEEN '2018-01-01' AND '2020-01-01')

Note: Use 'HH' for 24 hours format and 'hh' for 12.

edited Oct 22, 2020 at 12:43

answered Oct 22, 2020 at 12:22

Habib

411 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to convert datetime.datetime to datetime.date?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related