CODEWITHSUNDEEP
db2
Alaxi04
Alaxi04

Reputation: 21

db2 datetime conversion to date issue

I have date column in following format:
20120714042548334
20120714042548334
20120714042549375

Trying to query the table for a certain range, I am getting an error that says "The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007", error outs right away or after few thousand records are displayed. Query I am using is 

select * from AUDIT
where date(substr(serv_start_date_time,1,4)||'-' ||substr(serv_start_date_time,5,2) ||'-'||
substr(serv_start_date_time,7,2)) between date('9/4/2012') and date('9/10/2012')

Any help or assistance will be appreciated.

Upvotes: 0

Views: 2496

Answers (2)

WarrenT
WarrenT

Reputation: 4532

Have you tried putting your range dates in ISO format:

select * from AUDIT
where date( substr(serv_start_date_time,1,4) ||'-'
          ||substr(serv_start_date_time,5,2) ||'-'
          ||substr(serv_start_date_time,7,2)
          ) between date('2012-09-04') and date('2012-09-10')

Or faster, by avoiding type conversions, stick to character comparison since it seems that's what your data column is to begin with.

select * from AUDIT
where serv_start_date_time between '20120904' and '20120910'

Upvotes: 1

Plasmarob
Plasmarob

Reputation: 1391

Regex:

If you can provide more details as to how you're accessing it that would help. I access SQL via ruby - different situation, but knowing what tools you have at your disposal would help.

If you can use regular expressions at all, /([\d]{4})([\d]{2})([\d]{2})[\d]{9}/ will return: 2012 07 04 for 20120714042548334

Query suggestion:

Also, If the column is Date, Something to the effect of

SELECT Date FROM AUDIT WHERE Date>=20120714000000000 AND Date<20120824000000000

Should pull out everything between July 14 and August 23, for example.

In your 9/4 to 9/10, it'd be:

SELECT Date FROM AUDIT WHERE Date>=20120904000000000 AND Date<20120911000000000

Hope that helps.

Upvotes: 0

Related Questions