2
votes

I am saving the date as varchar in db2 with yyyy/mm/dd format i need to convert it to date datetype in the query how to achieve this?

I tried

select DATE(CRDTR2) from ASAODLIB.SSLR204 where CRDTR2 BETWEEN '2015/03/01' AND '2015/03/31';

query and got the below error

The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.68.61

can someone help me.

2
What platform and version of DB2?Charles

2 Answers

3
votes

If your DB2 version is new enough, use to_date: select DATE(TO_DATE(CRDTR2, 'YYYY/MM/DD')) from ...

1
votes

...because it doesn't recognize that format. I'd turn it into *ISO first, via REPLACE:

SELECT DATE(REPLACE(CRDTR2, '/', '-'))
FROM ASAODLIB.SSLR204 
WHERE CRDTR2 BETWEEN '2015/03/01' AND '2015/03/31'

Incidentally, there's a couple other things here.

  1. You should be storing dates as an actual date type, which would make this a non-issue.
  2. You shouldn't use BETWEEN, in preference for an exclusive upper-bound (< - the blog talks about SQL Server, but the problem is really due to representation. That, and most versions of DB2 allow you to specify fractional seconds in timestamps...).