date and time in the where condition

Database
Enthusiast

date and time in the where condition

i need to use the date and time in the where condition. Not sure how to get it work.

 

selct * from table

where cre_ts > current_date() 12 AM and cre_ts <  current_date() 3 AM. 

 

Both 12 AM and 3 AM should be of current date. 

4 REPLIES
Senior Apprentice

Re: date and time in the where condition

Hi,

A number of ways to do this, you can use the following:

 

 SELECT *
 FROM t1
 WHERE cre_ts BETWEEN (DATE (FORMAT 'yyyy-mm-dd'))||' 00:00:00'
    AND (DATE (FORMAT 'yyyy-mm-dd'))||' 03:00:00';

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: date and time in the where condition

This is not working. it says invalid timestamp.

 

 

select *

from table

where

file_TS>=(CURRENT_DATE (FORMAT 'yyyy-mm-dd')) || '00:01:00' AND file_TS< (CURRENT_DATE (FORMAT 'yyyy-mm-dd')) || '03:00:00'

Junior Contributor

Re: date and time in the where condition

There's a space missing between the date and the time portion.

Teradata Employee

Re: date and time in the where condition

You can go like this :

select *
  from table
 where cre_ts > cast(current_date as timestamp(0))
   and cre_ts < cast(current_date as timestamp(0)) + interval '3' hour;