Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2010
    Posts
    11

    Unanswered: Need help in converting String to Date format

    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.

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    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.

  3. #3
    Join Date
    Jun 2010
    Posts
    11
    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....

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    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")

  5. #5
    Join Date
    Jun 2010
    Posts
    11
    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....

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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)

  7. #7
    Join Date
    Jun 2010
    Posts
    51
    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.

  8. #8
    Join Date
    Jun 2010
    Posts
    11
    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

  9. #9
    Join Date
    Jun 2010
    Posts
    11
    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.

  10. #10
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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)

  11. #11
    Join Date
    Jun 2010
    Posts
    11
    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.....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •