How to convert date to integer value in sql server


  • 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?

    Thursday, May 10, 2018 12:25 PM

All replies

  • declare @t table
    dt datetime
    insert @t
    values ('20170101'),('20170102')
    SELECT dt,DATEDIFF(dd,'18991230',dt) AS dateasint
    FROM @t
    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
    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