CODEWITHSUNDEEP
sql-serverdateintegertype-conversion
Federico Zancan
Federico Zancan

Reputation: 4894

SQL date values converted to integers

Ok, I can't understand this thing.

A customer of mine has a legacy Windows application (to produce invoices) which stores date values as integers.

The problem is that what is represented like '01.01.2002' (value type: date) is indeed stored in SQL Server 2000 as 731217 (column type: integer).

Is it an already known methodology to convert date values into integers (for example - I don't know - in order to make date difference calculations easier?)

By the way, I have to migrate those data into a new application, but for as much I googled about it I can't figure out the algorithm used to apply such conversion.

Can anybody bring some light?

Upvotes: 0

Views: 371

Answers (1)

Nicolas
Nicolas

Reputation: 933

It looks like the number of days since Jan 1st 0000 (although that year doesn't really exists).

Anyway, take a date as a reference like Jan 1st 2000 and look what integer you have for that date (something like 730121).

You then take the difference between the integer you have for a particular date and the one for your reference date and you that number of days to your reference date with the DATEADD function.

DATEADD(day, *difference (eg 731217 - 730121)*, *reference date in proper SQLServer format*)

You can adjust if you're off by a day a two.

Upvotes: 1

Related Questions