none
Convert datetime to integer RRS feed

  • Question

  • Hi,

    I have this datetime: '2002-12-20 11:59:59'

    I want to convert this to date (yyyy-mm-dd) to integer

    This works fine for: SELECT CONVERT(INT, GETDATE())

    But it doesn't work here: SELECT CONVERT(INT, '2002-12-20 11:59:59')

    I want to convert date to integer without passing through any varchar conversion/result.

    Please help!


    cherriesh

    Monday, August 5, 2013 6:46 AM

Answers

  • Hi Cherriesh, the integer value returned in this conversion refers to the number of days since Jan 01, 1900.  In your second query, the date you have entered is stored as a varchar, thus the conversion error you're seeing.  You can cast or convert this to a datetime and the query will succeed.

    Ex:

    SELECT CONVERT(INT, CONVERT(DATETIME,'2013-08-05 09:23:30'))
    
    SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME))
    

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, August 5, 2013 7:28 AM
    Moderator
  • Try,

    SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())


    Regards, RSingh

    • Marked as answer by cherriesh Wednesday, August 14, 2013 1:09 AM
    Monday, August 5, 2013 10:55 AM

All replies

  • Hallo Cherriesh,

    for smalldatetime and datetime the date value is stored as 0 for the 01-01-1900. For the other types that store a date, the date value is stored as 693595, which represents the number of days after the new internal base date of January 1, 0001.

    SELECT	DATEADD(dd, 1, '19000101')
    SELECT	DATEADD(dd, 693595, CAST('01/01/0001' AS datetime2))

    To revert the passed days until 01-01-1900 you have to consider the data type of your stored date. Concerning smalldatetime or datetime the calculation is as follows:

    SELECT	DATEDIFF(dd, '19000101', getdate())


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Monday, August 5, 2013 7:22 AM
  • Hi Cherriesh, the integer value returned in this conversion refers to the number of days since Jan 01, 1900.  In your second query, the date you have entered is stored as a varchar, thus the conversion error you're seeing.  You can cast or convert this to a datetime and the query will succeed.

    Ex:

    SELECT CONVERT(INT, CONVERT(DATETIME,'2013-08-05 09:23:30'))
    
    SELECT CONVERT(INT, CAST ('2013-08-05 09:23:30' as DATETIME))
    

    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/b/samlester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, August 5, 2013 7:28 AM
    Moderator
  • Also you can try Dateformat option,

    declare @testdate datetime='2002-12-20 11:59:59'
    
    select  cast(CONVERT(varchar(20),@testdate,112) as INT)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Matt Tolhurst Monday, August 5, 2013 2:26 PM
    Monday, August 5, 2013 10:47 AM
  • Try,

    SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE())


    Regards, RSingh

    • Marked as answer by cherriesh Wednesday, August 14, 2013 1:09 AM
    Monday, August 5, 2013 10:55 AM
  • Hi All and Uwe Ricken

    Always I thought that it is simply conversion refers to the number of days since Jan 01, 1900. 

    I executed script:

    declare @d1 datetime = '2015-08-05 03:27:47'; declare @d2 datetime = '2015-08-05 23:27:47'; select CAST(@d1 as int) union all select CAST(@d2 as int) -- Result:

    -- 42219 -- 42220


    I got different result. 

    Why? I have changed time but days stay same. Why hours have impact?

    Wednesday, August 5, 2015 8:02 AM
  • I got different result. 

    Why? I have changed time but days stay same. Why hours have impact?

    Rounding. Observe results if you cast as float.
    Thursday, May 19, 2016 3:02 PM