Tech Off Thread

7 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Date stored as an int in SQL Server

Back to Forum: Tech Off
  • User profile image
    RamblingGeek​UK

    Date is stored as 735572 how do I convert?

    I have used 

    DATEADD(DAY, CONVERT(INT, CONVERT(VARBINARY(8), INVHEAD.Invoice_Date)), CONVERT(DATE, '0001-01-01'))

    This is a day ahead?

    Doing the following 

    DATEADD(DAY, CONVERT(INT, CONVERT(VARBINARY(8), INVHEAD.Invoice_Date)-1), CONVERT(DATE, '0001-01-01'))

    Fixes the issue, is there a better way ?

  • User profile image
    cheong

    In MSSQL, int 0 is '1900-01-01' in datetime. So you need some adjustment

    SELECT CONVERT(datetime, 735572 - 693596)

    693596 is the integer value for '3799-01-01' in SQL

     

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    figuerres

    why store a date as an int ??? 

    some other app is doing this and you have to deal with it ?

     

  • User profile image
    GuddurGovind

    @RamblingGeekUK: Can you please explain your scenario more detailed? 

    Date is stored as 735572 how do I convert?  => do you have only this one value?

    or do you need to convert a more number same kind of data from any flat or other DB file to your SQL Table?

     

    if it is just one value or less then 10 values yes you can run your select query as cheong mentioned. if it is huge then there is a different way, so please let me know your requirement.

  • User profile image
    AndyC

    Select Cast(xxx As DateTime)

    Really not recommended though and with the newer types (like DateTime2) which you definitely should be using, isn't allowed.

     

  • User profile image
    cheong

    @AndyC: That needs some adjustment as 735572 clearly is not a value with counting start on '1900-01-01', when the expected answer is '2014-12-05'.

    Replace INVHEAD.Invoice_Date in the provided SQL select clause with 735572 to see the expected result.

    Recent Achievement unlocked: Code Avenger Tier 4/6: You see dead program. A lot!
    Last modified
  • User profile image
    thantai

    why store a date as an int ??? 

    some other app is doing this and you have to deal with it ?

     

     

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.