Trying to convert varchar to datetime

  • I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following error

    Syntax error during explicit conversion of VARCHAR value '20114' to a DATETIME field.

    Can some one help me in solving this?

  • I guess that would depend on exactly what date you think 20114 represents.

    SELECT '20114', CAST('20114'+0 AS DATETIME)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • in what manner you want to display this '20114'

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • savycara (7/23/2013)


    I am trying to convert 20114 which is stored as varchar to datetime but I am getting the following error

    Syntax error during explicit conversion of VARCHAR value '20114' to a DATETIME field.

    Can some one help me in solving this?

    This value can be interpreted as multiple date values (see possible dates below). What date is defined by the value "20114"?

    2020-01-14 (200114 / 20200114)

    2020-11-04 (201104 / 20201104)

    2002-01-14 (020114 YMD / 20020114)

    2014-01-02 (020114 DMY / 02-01-2014)

    2014-02-01 (020114 MDY / 01-02-2014)

    Because additional digits are required to get a valid date notation, the conversion fails. You need to add additional prefixing to get a valid numeric value of at least 6 but preferably 8 digits.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi (7/24/2013)


    Because additional digits are required to get a valid date notation, the conversion fails. You need to add additional prefixing to get a valid numeric value of at least 6 but preferably 8 digits.

    Nonsense. Here's 2 cases where no additional digits are required.

    SELECT '20114', CAST('20114'+0 AS DATETIME)

    ,DATEADD(day, 0+RIGHT('20114', 3), DATEADD(year, 0+LEFT('20114', 2), 0))

    I do agree though that clarification of the requirement is needed.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (7/24/2013)


    Nonsense. Here's 2 cases where no additional digits are required.

    Ok, I shouldn't use the phrase "valid date notation" but rather something like "a more deterministic or clear date notation". Blame it on my lack of the English language (non-native) :hehe:

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • I noticed this is a double post

    See http://www.sqlservercentral.com/Forums/Topic1476809-3411-1.aspx#bm1476906

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

    I need this column data to be in style 101

  • I tried this but it is not working

  • savycara (7/24/2013)


    Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

    I need this column data to be in style 101

    Hey,

    Try this....

    declare @var varchar(6) = '20112'

    DECLARE @var1 varchar(10)

    declare @month varchar(2)

    IF LEN(@var) > 5

    BEGIN

    SET @month = RIGHT(@var,2)

    SET @var1 = (select LEFT(@var,4) + REPLACE(SUBSTRING(@month,1,2),' ','0')+'01')

    select CONVERT(DATETIME,@var1,101)

    END

    ELSE

    BEGIN

    SET @month = RIGHT(@var,1)

    SET @var1 = (select LEFT(@var,4) + REPLACE(str(@month,2),' ','0')+'01')

    select CONVERT(DATETIME,@var1,101)

    END

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • savycara (7/24/2013)


    Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

    I need this column data to be in style 101

    Your first post states that you want output as datetime. If you want your output as style 101, it would have to be varchar or char. Which do you want? Here's datetime:

    SELECT *

    FROM (SELECT strDate = '20114' UNION ALL SELECT '201112') d

    CROSS APPLY (SELECT strDate2 = STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01') x

    CROSS APPLY (SELECT dtDate = CONVERT(DATETIME,x.strDate2,112)) y

    CROSS APPLY (SELECT Result = CONVERT(DATETIME,STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01',112)) z


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • @kapil

    Thank you

  • This is a duplicate thread. Let's shift over to the other thread where there are more posts. No more posts on this thread, please. It's just dividing up the answers and making things harder to follow. Thanks folks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply