Post reply

How to get date value from a number ( ex: 20161108) ?

  • November 8, 2016 at 6:48 am

    #315406

    Hi,

    I have this "20161108", I need this "2016-11-08 00:00:00.000", may be this pretty simple, but I am getting error when I try the below

    select convert(datetime,convert(float,20161108))

    please help.

    Thanks,

    Prabhu

  • November 8, 2016 at 6:54 am

    #1911176

    Converting to a float is the problem.

    Try converting to a varchar(10) instead of a float.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • November 8, 2016 at 6:56 am

    #1911177

    It's not that simple.

    Casting the float to datetime directly gets you 20161108 days after the 1st of January 1900, which, since 20161108/365.25 leaves us with 55 thousand years, exceeds the max for datetime of 31 December 9999 by roughly 47 millennia.

    Cast (or convert if you prefer) through string first.

    DECLARE @BadDate INT = 20161108;

    SELECT CAST(CAST(@BadDate AS CHAR(8)) AS DATETIME)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 8, 2016 at 7:00 am

    #1911181

    And as you have no time component, consider using the DATE datatype, rather than DATETIME ... if you do, you'll save 5 bytes per occurrence.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 8, 2016 at 7:09 am

    #1911183

    Assuming you want to convert a string to DateTime.

    CAST('20161108' AS DATETIME)

    If the string contains a value that cannot be converted to a true calendar date (ie: Feb 29, 2015), then the function will raise a conversion error.

    Alternately you can use the TRY_CAST function, which will return a result of NULL if the conversion doesn't work.

    TRY_CAST('20161108' AS DATETIME)

    Also, as mentioned above, a simple date value without time portion can be cast as Date rather than DateTime.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • November 8, 2016 at 7:55 am

    #1911193

    Hi All,

    the problem was on converting the value into float as micheal and gail said, the code given by gail works for me.

    Thanks gail and everyone for your quick response.

    -Prabhu

Viewing 6 posts - 1 through 5 (of 5 total)

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