Converting Datetime to INT

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

  • 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

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

  • DECLARE @date AS DATETIME

    SET @date = '2007/01/01'

    SELECT CONVERT(VARCHAR,@date,112)

    - Zahran -

  • 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