Need help in converting String to Date format

surbhit4u · #1 (**permalink**) 06-22-10, 02:52

Hi,

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.

agrawal.meet · #2 (**permalink**) 06-22-10, 07:08

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.

surbhit4u · #3 (**permalink**) 06-22-10, 07:47

Hi,

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

agrawal.meet · #4 (**permalink**) 06-22-10, 07:58

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.

Code:

select convert(datetime, "20100614")

surbhit4u · #5 (**permalink**) 06-22-10, 08:07

Ohh, Thats a problem then...
Whats the solution then ??

Isnt there any way by which I can manipulate the input String.. may be extracting individual components of date & then by manipulating some way..

I have to get it done by any means.
Can you suggest a way out....

pdreyer · #6 (**permalink**) 06-22-10, 08:20

There is no way with your date format
As already mentioned you'll have to first change it to a supported style
e.g.

Code:

select convert(datetime,stuff(stuff(stuff('20100614191522',13,0,':'),11,0,':'),9,0,' '),112)

agrawal.meet · #7 (**permalink**) 06-22-10, 08:27

Before using the solution provided by pdreyer as standard..you will have to make sure that users don't use any other format to enter date from front-end.

I suggest you ask users to enter date in YYYYMMDD format..as this is ASCII standard and can be converted easily without string manipulation.

surbhit4u · #8 (**permalink**) 06-22-10, 08:42

Hi pdreyer,
Thanks for the query... Its working..

Can we get the expected output for the below input also.
Input 2: 2010/12
Expected output 2: 12/1/2010 12:00:00 AM

We need to consider "1" as default date, if thats not mentioned.

Thanks in advance

surbhit4u · #9 (**permalink**) 06-22-10, 08:44

Hi agrawal.meet,

Thanks for cautioning me.. As I said earlier, I cannot ask user to modify input.
They need to enter exact time with date for their business needs.

I also have inputs which are without time. I do not have prob at those places.
But only when timestamp is also included or when date is not entered, then theres a problem.

pdreyer · #10 (**permalink**) 06-22-10, 09:11

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

Code:

select case when charindex('/',x)=0
       then convert(datetime,stuff(stuff(stuff(x,13,0,':'),11,0,':'),9,0,' '),112)
       else convert(datetime,stuff(x,5,0,'/01'),111)
       end
from (select 
'20100614191522' union all select 
'2010/12'
)f(x)

surbhit4u · #11 (**permalink**) 06-22-10, 09:33

Wow,
Thats gr8 pdreyer...

Thats a cool query... U r a GEEK..
Thanks a looooooot for all ur help.. U helped in removing a big headache.....

Internet Services

Web Development

Digital Marketing

Consumer Tech