Using CAST to convert timestamp

Database
Highlighted

Using CAST to convert timestamp

I have a column with a timestamp and I only want to return the date portion using BETWEEN. My query isn't returning any data.

 

Select *
from Table
Where cast(rec_crt_dt as Date FORMAT 'YYYYMMDD') between 2016-11-1 and 2017-11-1;

5 REPLIES
Teradata Employee

Re: Using CAST to convert timestamp

The way it is written here it looks like arithmetic, i.e. 2016-11-1 looks like the number 2004.

Try:  between DATE'2016-11-01' and DATE'2017-11-01'.
Also: between 20161101 and 20171101  -- might work, depending on your mode.

Re: Using CAST to convert timestamp

Got an error "expected something like 'AND' keyword between the 'Date' keyword and the integer '2016'

 

Select *
from table
Where cast(rec_crt_dt as Date FORMAT 'YYYYMMDD') BETWEEN Date 2016-11-01 AND Date 2017-11-01;

Junior Contributor

Re: Using CAST to convert timestamp

When you compare dates and timestamps the timestamp is automatically casted to a date:

 

Where rec_crt_dt between DATE '2016-11-01' and DATE '2017-11-01';

 

But instead of casting the column to date it might be better to use timestamps literals:

 

Where rec_crt_dt >= TIMESTAMP '2016-11-01 00:00:00' 
  and rec_crt_dt <  TIMESTAMP '2017-11-02 00:00:00';

 

Btw, do you really want to include rows from '2017-11-01'?

 

 

Junior Contributor

Re: Using CAST to convert timestamp

Regarding the error message, date (literals) must be enclosed in single quotes:

Date '2016-11-01' instead of Date 2016-11-01 

Re: Using CAST to convert timestamp

That worked! Thank you.