Simple Date Query Not So Simple

icurschwarz · #1 (**permalink**) 05-20-09, 10:04

I'm fairly new to SQL and to this point my experience is mostly limited to Access. However, I'm trying to create a pass through query to a DB2 table and I'm having all kinds of headaches trying to set it up.

The query is very simple:

Code:

SELECT *
FROM TABLENAME.REQT
WHERE REQT_TMSP > '2006-01-01'

The REQT_TMSP field is a time stamp of when that request record was added to the table and when I view the table the format is:

MM/DD/YYYY 00:00:00 AM/PM

I've tried a ton of stuff to get this query to work but I keep getting syntax related errors. There is probably something very basic that I am missing but I can't seem to figure it out and I'm not finding much in Google that's helping.

Any ideas?

Stealth_DBA · #2 (**permalink**) 05-20-09, 10:13

icurschwarz, the timestamp format in DB2 is:

'YYYY-MM-DD-HH.MM.SS.000000'
(The last 6 digits are the micro seconds).

There are a few ways to fix your query.

WHERE REQT_TMSP > TIMESTAMP('2006-01-01', '24.00.00')
WHERE REQT_TMSP > '2006-01-01-24.00.00.000000'
(I don't recommend the next one if REQT_TMSP is indexed or could be indexed.)
WHERE DATE(REQT_TMSP) > '2006-01-01'

Where are you viewing the result that shows the AM/PM in the Timestamp?