none
How to convert date to integer value in sql server

    Question

  • Hi team,

    I have a quick question

    where i would convert date value to integer value ,can you some one please provide the query for that i tried couple of ways but

    still need not get the right query

    Input               Output

    01/01/2017      42736  
    02/01/2017      42737  

    How do we convert the above input value into the output value?

    • Moved by Olaf HelperMVP Saturday, July 14, 2018 3:00 AM Moved from "Database Design" to a more related forum
    Thursday, May 10, 2018 12:25 PM

Answers

  • declare @t table
    (
    dt datetime
    )
    
    insert @t
    values ('20170101'),('20170102')
    
    SELECT dt,DATEDIFF(dd,'18991230',dt) AS dateasint
    FROM @t
    
    
    /*
    Output
    -------------------------
    dt	                dateasint
    ----------------------------------
    2017-01-01 00:00:00.000	42736
    2017-01-02 00:00:00.000	42737
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 10, 2018 12:31 PM

All replies

  • declare @t table
    (
    dt datetime
    )
    
    insert @t
    values ('20170101'),('20170102')
    
    SELECT dt,DATEDIFF(dd,'18991230',dt) AS dateasint
    FROM @t
    
    
    /*
    Output
    -------------------------
    dt	                dateasint
    ----------------------------------
    2017-01-01 00:00:00.000	42736
    2017-01-02 00:00:00.000	42737
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 10, 2018 12:31 PM
  • Just to be clear.

    Your output seems to be a "Julian Date".  This is normally a count of days from a start date.  In your case your number appears to be based on a start date of 1899-12-30.  Different applications and solutions use different start dates.

    So the code Visakh provided simply calculates the number of days between 1899-12-30 and your date.

    Thursday, May 10, 2018 12:47 PM
    Moderator