All Forums Database
24 Jan 2014
Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

Hi Experts,
 
I have the below query in whcih i need to compare timestamp value 'YYYY-MM-DDBHH:MI:SS.S(6)  with
a date value in format   '01/23/2014 11:53:50.000000' as below , but i am getting error as invalid timestamp
  
CAST(SUBSTRING (CAST (A.DW_LOAD_TMP AS VARCHAR(19)) ,1,19) ) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')>
CAST(CAST ( SUBSTRING('01/23/2014 11:53:50.000000',1,19) AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')
 
 
Please suggest.
 
 
Regards,
Nishant

Tags:
dnoeth 4628 posts Joined 11/04
24 Jan 2014

Hi Nishant,
works for me in both BTEQ and SQL Assistant over ODBC/.NET:

SELECT   '01/23/2014 11:53:50.000000' AS ts,
   ts (TIMESTAMP, FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)') AS a,
   CAST(SUBSTR (CAST (ts AS VARCHAR(19)) ,1,19) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') AS b;

 *** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.

ts                                                   a                    b
--------------------------  --------------------------  -------------------
01/23/2014 11:53:50.000000  01/23/2014 11:53:50.000000  01/23/2014 11:53:50

Which client do you use?
Btw, unless you want less fractional seconds there's no need to do a SUBSTRING

Dieter

Santanu84 122 posts Joined 04/13
26 Jan 2014

Hi Nishant
Below worked for me.
 

 

SELECT

CAST(SUBSTRING (CAST ('01/23/2014 11:53:50.000000' AS VARCHAR(19)) ,1,19) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') AS TS1 ,

CAST(CAST ( SUBSTRING('01/23/2014 11:53:50.000000',1,19) AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')  AS TS2 ,

CASE

WHEN CAST(SUBSTRING (CAST ('01/23/2014 11:53:50.000000' AS VARCHAR(19)) ,1,19) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') >= 

CAST(CAST ( SUBSTRING('01/23/2014 11:53:50.000000',1,19) AS VARCHAR(19)) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') 

THEN 

'GE TIME'

ELSE

'NO MATCH'

END  AS TS_TEXT

 

 

 

TS1                            TS2                          TS_TEXT

1/23/2014 11:53:50 1/23/2014 11:53:50 GE TIME

 

 

Adeel Chaudhry 773 posts Joined 04/08
02 Feb 2014

You have 1 extra ')' just before AS TIMESTAMP(0):

 

Correct:

CAST(SUBSTRING (CAST (A.DW_LOAD_TMP AS VARCHAR(19)) ,1,19) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS') 

 

With extra ')'

CAST(SUBSTRING (CAST (A.DW_LOAD_TMP AS VARCHAR(19)) ,1,19) ) AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYBHH:MI:SS')

-- If you are stuck at something .... consider it an opportunity to think anew.

plato 1 post Joined 09/14
11 Sep 2014

Hi Dieter,
Can you help me withe the below logic
END_EFF_DATE=(BEG_EFF_DT of next record)-  1 micro second.
For me the MAX (END_EFF_DT) is 31/12/9999.....thats y using the below format.
------------------
I am using the below but its throwing error:
Select
OREPLACE(

CAST((

COALESCE(

MAX(A.MIN_BEG_EFF_DATE) OVER (PARTITION BY A.SUBSCBR_NUM,A.MBR_SUFFIX,A.CLAIM_NUM ORDER BY A.MIN_BEG_EFF_DATE DESC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),

(CAST('01-01-1000 00:00:00.000000' AS FORMAT 'MM-DD-YYYYBHH:MI:SS.S(6)')

)) - INTERVAL '1' SECOND) AS VARCHAR(50)

 

),'0999-','9999-')

 

AS END_EFF_DATE

FROM

Select SOME_DATE as END_EFF_DATE from <TABLE_NAME>
 
 

dnoeth 4628 posts Joined 11/04
11 Sep 2014

Why don't you simply tell which error is returned?
This fits your narration:

END_EFF_DATE
   = MAX(BEG_EFF_DT) 
         OVER (PARTITION BY A.SUBSCBR_NUM,A.MBR_SUFFIX,A.CLAIM_NUM 
         ORDER BY A.MIN_BEG_EFF_DATE 
         ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
     - INTERVAL '0.000001' SECOND

 

Dieter

Langaliya.Nirav 15 posts Joined 04/11
12 Jun 2015

how to convert string like Wed Jun 10 10:43:14 2015 to Date or timestamp format?

--Nirav Langaliya

dnoeth 4628 posts Joined 11/04
12 Jun 2015

Hi Nirav,
Teradata doesn't seem to like the time in between month and year.
But you can utilize TO_DATE or TO_TIMESTAMP:

TO_DATE(s, 'dy mon dd hh:mi:ss YYYY')

 

Dieter

You must sign in to leave a comment.