Timestamp format on teradata retrieval

Database
jas
N/A

Timestamp format on teradata retrieval

I have a parameter string coming into an ETL application which is in the format MM/DD/YYYY HH:MI:SS.

On the teradata database the rows I need to retrieve contain a timestamp in standard teradata format YYYY-MM-DD HH:MI:SS.

I need to retrieve rows whose timestamp is later than that in the paramater, but I cannot manage to write a successful query which will convert my parameterised input into an acceptable timestamp for teradata for comparison.

Any help with this would be greatly appreciated.

Rgds

Jas
13 REPLIES
Junior Contributor

Re: Timestamp format on teradata retrieval

Hi Jas,

select '03/07/2006 12:49:29' as ts,
ts (timestamp(0), format 'MM/DD/YYYYBHH:MI:SS')

Dieter
Enthusiast

Re: Timestamp format on teradata retrieval

Hi Dieter

can u explain me how it is showing 2006-03-07 12:49:29 for the next part. i am amazed the format u have mentioned i.e ts (timestamp(0), format 'MM/DD/YYYYBHH:MI:SS')
in the second part and the result it's showing.

Re: Timestamp format on teradata retrieval

Hi ,

Default timestamp format : yyyy-mm-dd hh:mm:ss

sel current_timestamp

2006-03-10 07:17:58.37

So specify the desired format: as

ex1:

select '03/07/2006 12:49:29' as ts,
cast(cast(ts as timestamp(0) format 'MM/DD/YYYYBHH:MI:SS') as varchar(20))

out put:

03/07/2006 12:49:29 03/07/2006 12:49:29

Note:Here one more varchar cast is required to display.

ex2 :

select
cast(cast('03/07/2006 12:49:29' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SS') as varchar(20))

o/p :03/07/2006 12:49:29

Thanks,

Enthusiast

Re: Timestamp format on teradata retrieval

i have one small question for you dnoeth if i want to add AM or PM in this timestamp how to write the query
for ex :
select cast(cast('03/07/2006 12:49:29 AM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SS AM') as varchar(20))
is it work in above select query,How to add Am or PM in Timestamp column? can you please help on this

Junior Contributor

Re: Timestamp format on teradata retrieval

select cast(cast('03/07/2006 12:49:29 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22))

'T' is used for 12-hour format and 'B' for blanks, check the "SQL Datatypes and Literals" manual, there's a chapter on FORMATs.

Dieter

Re: Timestamp format on teradata retrieval

Hi

I want to have current_date in YYYYMMDDHHMISS format. But whatever do I land with seperators. Kindly help

Thanks

benjamin

Junior Contributor

Re: Timestamp format on teradata retrieval

I assume you try it in SQL Assistant:

select current_timestamp (format 'YYYYMMDDHHMISS') (char(16))

Dieter

Re: Timestamp format on teradata retrieval

From

12/8/2010 8:00:40 PM 

8/30/2011 5:10:35 PM 

To

12/08/2010 20:00:40

08/30/2011 17:10:35

Suppose datetime column is start_date

Query

CAST(

CAST(

CAST(

CASE WHEN INDEX(START_DATE,' ')=0 THEN NULL

ELSE

CASE

WHEN SUBSTRING(START_DATE FROM 1 , INDEX(START_DATE,'/')-1 )>9

THEN SUBSTRING(START_DATE FROM 1 , INDEX(START_DATE,'/')-1 )

ELSE '0'||SUBSTRING(START_DATE FROM 1 , INDEX(START_DATE,'/')-1 ) END ||'/'||

CASE WHEN SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1 , INDEX(SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1),'/')-1 )>9

THEN SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1 , INDEX(SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1),'/')-1 )

ELSE '0'||SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1 , INDEX(SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1),'/')-1 )

END ||'/'||

SUBSTRING (SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1) FROM INDEX(SUBSTRING(START_DATE FROM INDEX(START_DATE,'/')+1),'/')+1,4)

||' '||

CASE WHEN SUBSTRING( SUBSTRING(START_DATE FROM INDEX(START_DATE ,' ')+1) FROM 1, INDEX(SUBSTRING(START_DATE FROM INDEX(START_DATE ,' ')+1),':')-1 )>9

THEN SUBSTRING( SUBSTRING(START_DATE FROM INDEX(START_DATE ,' ')+1) FROM 1)

ELSE '0'||SUBSTRING( SUBSTRING(START_DATE FROM INDEX(START_DATE ,' ')+1) FROM 1)

END

END

AS TIMESTAMP(0) FORMAT 'mm/dd/yyyybhh:mi:ssbt')

AS TIMESTAMP(0) FORMAT 'mm/dd/yyyybhh:mi:ss')

AS VARCHAR(23))

Re: Timestamp format on teradata retrieval

Dieter and all, I have a timestamp without seconds that I want to keep as a timestamp & not a VARCHAR like this: 5/27/2013 3:36 

When I try to create the table using the command belowI get an error that Teradata SQL Assist expected something like a 'CHECK' keyword or an 'UNIQUE' key word between the word 'trns_dt' and '('

trns_dt (timestamp(0), format 'MM/DD/YYYYBHH:MI:BB')

or  

trns_dt (timestamp(0), format 'MM/DD/YYYYBHH:MI')

Any thoughts on how to fix this?