convert string date(yyyy/mm/dd) to date format in db2

Question

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.

Answer 1

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

Answer 2

...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...).