Unanswered: Need help in converting String to Date format
I am trying to convert two types of string into date format, however not able to do either of them.
The problematic input strings with expected output are as follows: Input 1: 20100614191522 Expected output 1: 6/14/2010 7:15:22 PM
Input 2: 2010/12 Expected output 2: 12/1/2010 12:00:00 AM
I tried like , select convert(datetime,'20100614191522',109)
I tried with different style parameters with "convert" function . But I always get following errors.
Syntax error during explicit conversion of VARCHAR value '20100614191522' to a DATETIME field.
Msg: 249, Level: 16, State: 1
Can you please help me out, how to achieve the same.
Thanks in advance.
Converting the input you gave to date time is not possible without first modifying the input itself. With the current input, you will first have to do string manipulation and then convert which is not the correct as this may not apply to the second input.
Where are you getting these inputs from? Is there no standard that is maintained on the input?
Let me know if you were able to find anything. Best of luck.
The inputs, as they are entered by the user in our application.
I cannot change the inputs.
I know the 2nd input is problematic.
However , the first one seems to be ok
Input 1: 20100614191522
This is in YYYYMMDDHHMMSS format...... Y cant we change it to proper date format.. Am sure there must be some way....
Syabse doesn't know that they are in YYYYMMDDHHMMSS format. It can only convert string back to datetime if it's in proper format and understood by sybase. Sybase can't convert datetime to string format mentioned by you so the opposite is also not possible.
From your input, if we only take date portion and strip time portion, then syabse would be able to convert it to datetime.
Your database should store a datetime
i.e. Your app should convert the input to datetime before storing it in the database.
However, here is an example to solve your current problem
select case when charindex('/',x)=0
then convert(datetime,stuff(stuff(stuff(x,13,0,':'),11,0,':'),9,0,' '),112)
'20100614191522' union all select