CODEWITHSUNDEEP
db2
naveena
naveena

Reputation: 33

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

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.

Upvotes: 2

Views: 37443

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

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

Upvotes: 1

Derek Williams
Derek Williams

Reputation: 229

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

Upvotes: 3

Related Questions