string to timestamp(0) convesion

[shaik_feroz]

Hello
I have a varchar field that has dates in the following format
Aug 29 2007 10:45PM
08/30/07 10:45:00 PM

is there a UDF that converts these to timestamp(0)
in the following format 'yyyy-mm-dd hh:mm:ss' (24 hour clock)

or a sql to convert this will do.

Thanks

[shaik_feroz]

I got it for the first string conversion
FORMAT 'mmmBddByyyyBhh:miT'

Aug 29 2007 10:45PM (FORMAT 'mmmBddByyyyBhh:miT')

when i did something similar for the second one
08/30/07 10:45:00 PM (FORMAT 'mm/dd/yyBhh:mi:ssBT')
i got 1907-08-30 22:45:00 as the output.
is there a way to get it as 2007-08-30 instead of 1907-08-30

Thanks

[al1_24]

And how do you know 2007 is better than 1907 ?
You surely have a way to choose it : a condition.

I would just use a CASE :
SELECT chardate (FORMAT 'mm/dd/yyBhh:mi:ssBT') + CASE WHEN condition THEN INTERVAL 100 YEAR ELSE 0 END AS newdate
...

[shaik_feroz]

Thank you for the information.

[sethiyagu]

Hi Feroz,

Try this one,

Query: SELECT CAST([COLUMN_NAME] AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI:SSBZ') FROM DBC.[TABLE_NAME]

STRATION