db2 select previous day qry

[Poet]

ipeoples. I am not sure where you are doing your query to get that format of a Timestamp in you result but it is just how that process is displaying it. DB2 Timestamp format is YYYY-MM-DD-00.00.00.000000.

However, if your tables column CREATED_DATE data type is timestamp then you only need:

SELECT *
FROM table
WHERE CREATED_DATE > CURRENT TIMESTAMP - 1 DAY

I've found that CURRENT TIMESTAMP - 1 DAYS can return results limited by the time of day returned by CURRENT TIMESTAMP. For instance, if it's 3:39 PM on 1/2/2015, then CURRENT TIMESTAMP - 1 DAYS would actually return 3:39 PM on 1/1/2015. To return all results for the previous day, regardless of time, I used the below:

SELECT *
FROM table
WHERE (CREATED_DATE > varchar_format(current timestamp - 1 DAYS, 'YYYY-MM-DD')) AND (CREATED_DATE < varchar_format(current timestamp, 'YYYY-MM-DD'));

Sorry for replying to a dead post, but it showed on Google as a result for an issue I was having and I wanted to ensure other viewers could be helped.