Post reply

Converting Datetime to INT

  • August 6, 2008 at 9:45 am

    #190722

    Dear all,

    I have a value of 2007/01/01 which I would like to convert to INT ie 20070101

    I have the following query

    SELECT CAST('2007/01/01' as int) as t

    But i get the following error msg

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value '2007/01/01' to data type int.

    Is there a way of doing this?

  • August 6, 2008 at 9:58 am

    #853952

    cast(replace('2007/01/01', '/', '') as int)

    If you cast/convert a datetime value to Int, you'll end up with the number of days since 1/1/1900, not the format you're looking for. The above should give you what you're looking for.

    I'm curious as to why you'd want to cast it to an integer in the above format.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 6, 2008 at 10:05 am

    #853960

    Thanks for you reply.

    The reason is the date (a parameter to a report) which I am getting is in 2007/01/01 format however the date in the database is in int format ie 20070101. Therefore when I do a comparsion I will never get a result as the dates are in different formats..

  • August 6, 2008 at 10:10 am

    #853967

    DECLARE @date AS DATETIME

    SET @date = '2007/01/01'

    SELECT CONVERT(VARCHAR,@date,112)

    - Zahran -

  • August 6, 2008 at 10:16 am

    #853972

    Thanks, both replies work 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply