UDB date query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smp7dg@gmail.com - 27 Jan 2006 19:37 GMT

I'd like to write a query which will get all results from last month
from a table.

It should look something like this:
Select [fields] from [table] where [timestamp field] >= '[first day of
last month at midnight]'

I cannot hardcode the date or programatically generate it. The same
exact query must be used every month.

Is this possible? How can I do this?

Reply to this Message

smp7dg@gmail.com - 27 Jan 2006 20:10 GMT

figured it out..if anyone is interested

SELECT [FIELDS] from [TABLE] where [TIMESTAMP FIELD] >= (SELECT current
timestamp - HOUR (current timestamp) HOURS
- MINUTE (current timestamp) MINUTES
- SECOND (current timestamp) SECONDS
- MICROSECOND (current timestamp) MICROSECONDS
- 1 MONTH
FROM sysibm.sysdummy1)

Reply to this Message

Tonkuma - 28 Jan 2006 02:14 GMT

Select [fields]
from [table]
where [timestamp field] >=
CURRENT_TIMESTAMP - 1 MONTH
- (DAY(CURRENT_DATE - 1 MONTH) - 1) DAYS
- MIDNIGHT_SECONDS(CURRENT_TIME) SECONDS
- MICROSECOND(CURRENT_TIMESTAMP) MICROSECOND

Reply to this Message

Brian Tkatch - 30 Jan 2006 16:10 GMT

WHERE DATE([timestamp field]) BETWEEN
CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS - 1 MONTH
AND
CURRENT DATE - DAY(CURRENT DATE) DAYS

B.

Reply to this Message