CODEWITHSUNDEEP
t-sqlsql-server-2005type-conversion
Gryphoenix
Gryphoenix

Reputation: 229

How can I convert this INT field to a usable date field?

The DB I am working in stores their date values as INT. I am needing to determine a date difference between two date fields but am unsure how to approach this since they are currently INT.

Dates are stored as follows: 20130101 - YYYYDDMM. Performing a DATEDIFF results in an arithmetic overflow error.

Any ideas on how to either convert two date fields or to find the date difference between them?

Upvotes: 1

Views: 253

Answers (1)

Thomas
Thomas

Reputation: 64645

Select Cast( Cast( 20130101 As varchar(8) ) As datetime )

So, if you have the following two values: 20130101, 20130201, we might do the following:

Select DateDiff( d
    , Cast( Cast( 20130101 As varchar(8) ) As datetime )
    , Cast( Cast( 20130201 As varchar(8) ) As datetime ) )

Update

If you have values that less than 17530101 (the min value for a datetime), then you will need to use a Case expression to validate the data as it is processed. In this scenario, the use of Cast(0 As datetime) will result in 1900-01-01 as our minimum date value.

Select DateDiff( d
    , Case
        When Value1 <= 19000101 Then Cast(0 As datetime)
        Else Cast( Cast( Value1 As varchar(8) ) As datetime )
        End
    , Case
        When Value2 <= 19000101 Then Cast(0 As datetime)
        Else Cast( Cast( Value2 As varchar(8) ) As datetime )
        End )

If you are going to store the dates as integers as shown, a better idea would be to correct the data and add a check constraint that prevents values lower than 1900-01-01. If you have legitimate values lower than 1900-01-01, that will require yet another adjustment to the solution.

Upvotes: 3

Related Questions