All Forums Database
jas 1 post Joined 03/06
07 Mar 2006
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. RgdsJas

dnoeth 4628 posts Joined 11/04
07 Mar 2006

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

Dieter

dixon 22 posts Joined 01/06
10 Mar 2006

Hi Dietercan 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.

Dixon

Sathish 3 posts Joined 11/05
10 Mar 2006

Hi , Default timestamp format : yyyy-mm-dd hh:mm:sssel current_timestamp 2006-03-10 07:17:58.37So 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:29Note: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:29Thanks,

yateesh 5 posts Joined 05/11
22 Jun 2011

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

dnoeth 4628 posts Joined 11/04
22 Jun 2011

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

Dieter

benjaminpimkov 1 post Joined 11/11
30 Nov 2011

Hi

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

Thanks

benjamin

 

dnoeth 4628 posts Joined 11/04
30 Nov 2011

I assume you try it in SQL Assistant:

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

Dieter

Dieter

kashifkiani 1 post Joined 11/09
19 Oct 2012

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

gtsoccer 1 post Joined 06/13
26 Jun 2013

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?

dnoeth 4628 posts Joined 11/04
26 Jun 2013

Your syntax is wrong, within a Create Table it's
trns_dt timestamp(0) format 'MM/DD/YYYYBHH:MI'
 
Dieter

Dieter

drmkd17 54 posts Joined 10/12
30 Jul 2014

If I want to check if the timestamp coming from the source is valid or not as in with respect to the format and data how do i check.
Please let me know what validations can be done.
 

krishaneesh 140 posts Joined 04/13
30 Jul 2014

i do not think there is a way to validate the incoming data. Here is an example that may explain a date field coming in as 1212014. if we do not spexify a format this can be considered as Jan 21st or December 1st of 2014. Hence validating this would be difficult. That is why people prefer it to be in MMDDYYYY format.

dnoeth 4628 posts Joined 11/04
31 Jul 2014

How does your input data look like? Is there a formatting rule?
Based on yor actual needs you might use a Regular Expression in TD14 to check for valid date/time.

Dieter

You must sign in to leave a comment.