Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

Database
Enthusiast

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 (1)
9 REPLIES
Junior Contributor

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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

Enthusiast

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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

Teradata Employee

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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

N/A

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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>
Junior Contributor

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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
Enthusiast

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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

Junior Contributor

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

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')
K_Y
Enthusiast

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

Hi 

 

I want to cast a timestamp value to date value using to_date funtion Teradata sql assistant or bteq, have tried below 

 

SELECT TO_DATE ('4/25/2017 06:16:39', 'YYYY-MM-DD');

But It throws the error as below:

 

YYYY value must be faur digits and in the range of 1-9999

 

If I execute below 

SELECT TO_DATE (upd_date, 'YYYY-MM-DD') from Table;

Here upd_date contains timestamp value, then it throws below error:

 

Function TO_DATE called with an invalid number or type of parameters.

 

Please let me know how to achive above with to_date function only, I don't want to use cast function.

 

 

Junior Contributor

Re: Converions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'

TO_DATE is used to cast a VarChar to a date and the 2nd parameter is the format used for converting.

 

In your 1st query the format doesn't match the input string and in your 2nd query you already got a Timestamp.

 

Try CAST(upd_date AS DATE)