SQL: Convert VARCHAR to Date Format
Posted on 2005-05-02
I'm working with a table that receives data from a LDAP, and since it is character based, the date/time fields are imported in as type VARCHAR.
I'm trying to manipulate data with different data ranges and having problems converting VARCHAR to date format.
Examples of 'time' field in VARCHAR:
For some reason I am able to use the function date(time) in the select, in another words, I can do:
select time, date(time) from table 1
and receive valid conversions:
However the problem occurs when I apply this to the where clause:
where date(time) > '2005-4-30';
The following error is returned:
[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
Any advice is appreciated. Thanks.