db2 select previous day qry
Results 1 to 4 of 4

Thread: db2 select previous day qry

  1. #1
    Join Date
    Mar 2011
    Posts
    3

    Post db2 select previous day qry

    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.

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    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:
    Code:
    SELECT *
    FROM table
    WHERE CREATED_DATE > CURRENT TIMESTAMP - 1 DAY

  3. #3
    Join Date
    Mar 2011
    Posts
    3
    Quote Originally Posted by SDas View Post
    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:
    Code:
    SELECT *
    FROM table
    WHERE CREATED_DATE > CURRENT TIMESTAMP - 1 DAY
    Thanks SDas, IT works great!

  4. #4
    Join Date
    Jan 2015
    Posts
    1
    Quote Originally Posted by SDas View Post
    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:
    Code:
    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:

    Code:
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •