convert int to smalldatetime

Posted on 2007-07-31
Last Modified: 2012-06-22

How do I convert an int to a smalldatetime?
The int has a format of yyyyMMddmmss. For example, 200707302345 means 7/30/2007 23:45.

Question by:sdc248
    LVL 68

    Accepted Solution

    SET @value = 200707302345
    SELECT CAST(STUFF(STUFF(CAST(@value AS VARCHAR(12)), 9, 0, ' '), 12, 0, ':') AS SMALLDATETIME)

    [Btw, that value won't fit into an INT ... maybe it's a BIGINT?]
    LVL 10

    Assisted Solution

    int is too small, it will have to be a bigint.  E.g.:

    declare @intdatetime bigint
    declare @sdtdatetime smalldatetime
    set @intdatetime = 200707302345

    set @sdtdatetime = convert (smalldatetime,
    substring (cast (@intdatetime as nvarchar), 1, 4) + '-' +
    substring (cast (@intdatetime as nvarchar), 5, 2) + '-' +
    substring (cast (@intdatetime as nvarchar), 7, 2) + ' ' +
    substring (cast (@intdatetime as nvarchar), 9, 2) + ':' +
    substring (cast (@intdatetime as nvarchar), 11, 2)
    , 120)

    select @sdtdatetime

    Author Comment

    The value is in decimal(12,0). Sorry my mistake. Is this the reason why I am getting NULL using your solution?

    Author Comment

    Oh, lahousden's solution works. Thanks a lot.
    LVL 68

    Expert Comment

    I think only if the input value is NULL (that is, was not set).

    Author Comment


    I did some more tests and found that your solution was right, it was me making a mistake when testing it.  Now the points have been given to lahousden.  Do you know any way I can give you the same amount of points as well? Sorry.
    LVL 68

    Expert Comment

    Thanks, I appreciate that.

    Depends on how many pts the initial q was for?  I think it was 150, but am not sure now.  If it was, you can open a q with a title like "Points for ScottPletcher for Q_22732029".  Remember, though, that according to EE rules, you cannot award more than 500 pts for any one q.

    Author Comment

    I have doubled the assigned points (125, now 250) and splited it so ScottPletcher can get the points he deserves.

    Thanks a lot guys.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now