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

the db working in stores date values int. needing determine date difference between 2 date fields unsure how approach since int.

dates stored follows: 20130101 - yyyyddmm. performing datediff results in arithmetic overflow error.

any ideas on how either convert 2 date fields or find date difference between them?

select cast( cast( 20130101 varchar(8) ) datetime ) 

so, if have following 2 values: 20130101, 20130201, might following:

select datediff( d     , cast( cast( 20130101 varchar(8) ) datetime )     , cast( cast( 20130201 varchar(8) ) datetime ) ) 

update

if have values less 17530101 (the min value datetime), need use case expression validate data processed. in scenario, use of cast(0 datetime) result in 1900-01-01 our minimum date value.

select datediff( d     , case         when value1 <= 19000101 cast(0 datetime)         else cast( cast( value1 varchar(8) ) datetime )         end     , case         when value2 <= 19000101 cast(0 datetime)         else cast( cast( value2 varchar(8) ) datetime )         end ) 

if going store dates integers shown, better idea correct data , add check constraint prevents values lower 1900-01-01. if have legitimate values lower 1900-01-01, require yet adjustment solution.