Type casting of String/Int to Date python

Question

While trying to execute my python script, I am facing these type conversion errors :

historical_start_date = '2015-12-01'
historical_end_date = '2015-12-31'
caldate = 2015-12-10 ## this is date type

sql_str = """SELECT  ART_TYPE as ART_TYPE,
                                year(event_dt) * 100 + month(event_dt) as year_month,
                                sum(measured_quantity)
                                FROM <tablename>
                                WHERE   EVENT_DT>=to_date('{1}','YYYY-MM-DD')"""
        if historical_end_date > caldate.strftime('%Y-%m-%d'):
                sql_str= sql_str+ " AND EVENT_DT<" +caldate.strftime('%Y-%m-%d')
        else:
                sql_str= sql_str+ " AND EVENT_DT <= to_date('{2}','YYYY-MM-DD') "
        sql_str= sql_str+ """ GROUP BY ART_TYPE,year(event_dt) * 100 + month(event_dt)""".format(historical_start_date,historical_end_date)

On running this, I get the following error :

('42883', '[42883] ERROR 4286:  Operator does not exist: date < int\nHINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts\n (4286) (SQLExecDirectW)')

Sounds like you're comparing a `varchar` representation of a date with an `int` representation of a date. Make sure your data types match: convert on type to the other type. — openwonk, Dec 18 '15 at 15:57
In the code above , I have tried to typecast caldate to string using : caldate.strftime('%Y-%m-%d'). Is it the right way to do it? I have tried otherways to make the data types equal on both sides of "=" . Could you provide some examples of conversions (syntax) for date to str, str to date, int to date — Data Enthusiast, Dec 18 '15 at 16:02
@Adriano, I have given .format at the end for the entire sql_str. Should I be adding format() for every string inside sql_str where I have a parameter {0}. Also I will try to use {something } by name — Data Enthusiast, Dec 18 '15 at 17:11

ciacicode · Answer 1 · 2015-12-18T21:15:29.317

0

In:

 if historical_end_date > caldate.strftime('%Y-%m-%d'):

it looks like you are comparing two strings. Comparing strings in Python is not the same as comparing time. Try to typecast the dates you have to datetime so you will know for sure you are comparing things that are built to be compared.

I say use the strptime() method:

from datetime import datetime
hist_end_dt = datetime.strptime(historical_end_date,'%Y-%m-%d')
caldate_dt = datetime.strptime(caldate,'%Y-%m-%d')
if hist_end_dt > caldate_dt:
    #do whatever you need to do

edited Dec 18 '15 at 21:15

answered Dec 18 '15 at 16:06

ciacicode

708
1
5
13

@ciaciode : I tried to use strptime() method , the error I got was datetime object from datetime cannot be accessed. I am using the import statement s : import datetime\n – Data Enthusiast Dec 18 '15 at 16:12
My bad @UdayShankar. The import statement should be different for my example to work. I will edit the answer. Let me know if that works. – ciacicode Dec 18 '15 at 21:14
I had to use both import statements : import datetime and from datetime import datetime because of which I am getting an error that datetime inside datetime cannot be accessed. Is there a workaround for this? – Data Enthusiast Dec 18 '15 at 23:31
I don't think there is a workaround given the way datetime is structured as a package. Most importantly though, did you manage to achieve your goal with this change? – ciacicode Dec 19 '15 at 16:35

Type casting of String/Int to Date python

1 Answers1