Type conversion

[bhosalenarayan]

Hi

What i am trying to do is to convert the string '20091212000019' to datetime format.
So whenever i fire the query:

SELECT CONVERT(DATETIME,'20091212000019')

It gives me this error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.


Whereas this one works just perfect:

SELECT CONVERT(DATETIME,'20091212')

Is there any way to sort the former????

Thanks

[bhosalenarayan]

I tried doing this.


SELECT (SUBSTRING(P_DATE,1,4))+'-'+(SUBSTRING(P_DATE,5,2))+'-'+(SUBSTRING(P_DATE,7,2))+' '+(SUBSTRING(P_DATE,9,2))+':'+(SUBSTRING(P_DATE,11 ,2))+':'+(SUBSTRING(P_DATE,13,2)) AS P_DATE FROM tablename

Then exported the data to desired table.

and it worked just fine. but is there any direct way, some function may be, to do this????

[skhanal]

Try using 12 instead of 00 for 12 AM.

[bhosalenarayan]

Dint work!! is there any direct function that converts char to datetime???

[rmiao]

Only convert and cast function, you may need format the string properly.

[bhosalenarayan]

Thanks a lot!!!