Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Query to Get Midningt timestamp

    How do i get midnight timestamp for yesterday? I need to select all the records which was submitted before midnight.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    midnight timestamp for yesterday
    I'm not sure wheather it is '2012-01-27 00.00.00' or '2012-01-26 00.00.00', if today is '2012-01-27'.

    If assumed the former, try ...
    current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS
    or
    TIMESTAMP(current_date)

    If you want the latter,
    minus one day from the expressions.
    Last edited by tonkuma; 01-26-12 at 21:59. Reason: Correct first sample expression(Add "SECONDS")

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    I'm not sure wheather it is '2012-01-27 00.00.00' or '2012-01-26 00.00.00', if today is '2012-01-27'.

    If assumed the former, try ...
    current_timestamp - MIDNIGHT_SECONDS(current_timestamp)
    or
    TIMESTAMP(current_date)

    If you want the latter,
    minus one day from the expressions.
    Today is 2012-01-26
    I need '2012-01-26 00.00.00'

    I got errors for both of these
    select TIMESTAMP(current_date) from sysibm.sysdummy1
    THE NUMBER OF ARGUMENTS SPECIFIED FOR TIMESTAMP IS INVALID. SQLCODE=-170, SQLSTATE=42605, DRIVER=3.58.81

    select (current_timestamp - MIDNIGHT_SECONDS(current_timestamp)) from sysibm.sysdummy1
    THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF - IS INVALID. SQLCODE=-171, SQLSTATE=42815, DRIVER=3.58.81

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version/release and platform OS are you using?

    The corrected expressions worked on my DB2 9.7.5 on Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES
    ( current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS
    , TIMESTAMP(current_date)
    );
    ------------------------------------------------------------------------------
    
    1                          2                  
    -------------------------- -------------------
    2012-01-27-00.00.00.585000 2012-01-27-00.00.00
    
      1 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The result of expression
    current_timestamp - MIDNIGHT_SECONDS(current_timestamp) SECONDS
    include subsecond difference.

    It would be better to use latter expression
    TIMESTAMP(current_date)

    If your DB2 doesn't support it, try
    TIMESTAMP( CHAR(current_date) )

Posting Permissions

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