Timestamp Format

Database
Enthusiast

Timestamp Format

Hi All,

I am having one column which is in timestamp(6) format in source and i need in below varchar  format.

Source Column data type: ASSOC_ASGN_TS TIMESTAMP(6),

Target Column data type: varchar(26)

Desired Format: 2014-06-27-20.19.04.757246

SEL CAST(ASSOC_ASGN_TS  as varchar(26)) FROM ICDW_XLOB_GBL_V.INVL_PRTY_ASSOC_CVR   -- 2014-06-27 20:19:04.757246

when I was using below query, it was showing error:

SEL CAST(CAST(ASSOC_ASGN_TS AS TIMESTAMP  FORMAT 'YYYY-MM-DD-HH24:MI:SS.US')  AS VARCHAR(26)) FROM ICDW_XLOB_GBL_V.INVL_PRTY_ASSOC_CVR  --Error

Could you please help me on this.

5 REPLIES
Enthusiast

Re: Timestamp Format

What was the error text?

Junior Supporter

Re: Timestamp Format

Hi:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(CAST(CURRENT_TIMESTAMP(6) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDbhh:mi:ss.s(6)') AS VARCHAR(26));

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

Current TimeStamp(6)
--------------------------
2014-07-29 15:40:12.220000

BTEQ -- Enter your SQL request or BTEQ command:

HTH.

Cheers.

Carlos.

Enthusiast

Re: Timestamp Format

Hi Glass,

Error: Invalid format String 'YYYY-MM-DD-HH24:MI:SS.US'

Hi Carlos, my desired format is  2014-06-27-20.19.04.757246

Enthusiast

Re: Timestamp Format

I am not Carlos :). It is the same what Carlos has given excepting a dot or bindu.

SELECT CAST(CAST(CURRENT_TIMESTAMP(6) AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDbhh.mi.ss.s(6)') AS VARCHAR(26));

Enthusiast

Re: Timestamp Format

Thank you Raja and Carlos..

its working ... :-)