db2 select previous day qry

[ipeoples]

Hi I need to select previous day on db2 database to automate a process.
Select *
from table
where created_date > (SELECT date(days(current date) - 1)
FROM SYSIBM.SYSDUMMY1)
This qry bring previous day in format '3/9/2011 12:00:00 AM' but I need the result in 'YYYY-MM-DD-00.00.00.000000' format in order to read from db2 table.

[SDas]

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

[ipeoples]

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

Thanks SDas, IT works great!

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