Query to Get Midningt timestamp

aramnaujam · #1 (**permalink**) 01-26-12, 19:37

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

tonkuma · #2 (**permalink**) 01-26-12, 20:35

Quote:

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.

aramnaujam · #3 (**permalink**) 01-26-12, 20:56

Quote:

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

tonkuma · #4 (**permalink**) 01-26-12, 21:04

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.

tonkuma · #5 (**permalink**) 01-26-12, 21:13

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) )

Internet Services

Web Development

Digital Marketing

Consumer Tech