3

I'm working with a database where dates are stored as 10 digit integers and where the user can query information within certain calendar date ranges. I was wondering what are the steps for converting a calendar date to a 10 digit integer for comparison with dates stored in a database.

I'm working with sql server 2000.

jsmith
  • 565
  • 3
  • 14
  • 28
  • 1
    Can you give an example of one of these 10 digit integers? `yyyymmdd` is only 8 digits so what are the other two? Also why is it stored like that rather than using `datetime` anyway? – Martin Smith Feb 24 '12 at 14:39
  • What algorithm is used to transform a date into a 10 digit integer? Is there a specific start date that would equal 0? – PinnyM Feb 24 '12 at 14:39
  • Do you want to convert the dates in the query? I think it would be better to convert the dates before you query the DB... What language are you using for the application? – Keeleon Feb 24 '12 at 14:39
  • An example would be 1240494225 -> 2009-04-23-13:43:45.000 and I think the start date is January 1st,1970 – jsmith Feb 24 '12 at 14:41
  • 1
    possible duplicate of [converting Epoch timestamp to sql server(human readable format)](http://stackoverflow.com/questions/4787827/converting-epoch-timestamp-to-sql-serverhuman-readable-format) – Martin Smith Feb 24 '12 at 14:42

1 Answers1

4

You need SELECT DATEADD(second, 1240494225, '19700101') to convert the number to a date and SELECT DATEDIFF(second, '19700101', @some_date) to go the other way.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845