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

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.

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,

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

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

Hi

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

Thanks

benjamin

I assume you try it in SQL Assistant:

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

Dieter

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