Post reply

Convert INT to Datetime

  • November 5, 2009 at 9:09 am

    #136109

    Guys,

    Would appreciate your help!

    I'm trying to convert a ddmmyyyy (23052008) integer to a datetime field using CAST and CONVERT but I'm getting an out of range datetime value?

    Could anyone shed any light on this?

    Many thanks,

    Gary

  • November 5, 2009 at 9:17 am

    #1075439

    You can convert it to a string and then work with the string manipulation functions. Here is an example:

    declare @i int

    declare @ci char(8)

    set @i = 23052008

    set @ci = convert(char(8), @i)

    select convert(smalldatetime,right(@ci, 4) + substring(@ci, 3, 2) + left(@ci,2))

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 5, 2009 at 9:17 am

    #1075440

    Try something like this:

    declare @DTInt int, @DTString varchar(20);

    select @DTInt = 23052008;

    select @DTString = cast(@DTInt as varchar(20));

    select cast(stuff(stuff(right(stuff(@DTString, 5, 0, left(@DTString, 2)), 8), 3, 0, '/'), 6, 0, '/') as datetime);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 5, 2009 at 9:20 am

    #1075443

    Many thanks to you both.

    G

  • November 5, 2009 at 9:21 am

    #1075444

    careful on day numbers < 10 though

    1Jan2009

    will be

    1012009 no the expected 01012009


    Clear Sky SQL
    My Blog[/url]

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

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