0

My problem is with a table that stores the date and time in two separate columns as integers. I know that's how sql server stores the dates internally - first 4 byte int for the dates after 1900 - 01 - 01 and the second 4 byte int for ticks after midnight, but the values I have don't correspond to this.

Direct example - Date:77262 , Time:3767327.

The time value converts to datetime (after binary(4) conversion) - 1900-01-01 03:29:17.757 but the date is not even close to what it should be and I'm sure it has to be somewhere in july 2012. I'm probably missing something fundamental but I really couldn't find any solution.

user1458381
  • 1
  • `dateadd(day, 77262, '1900-01-01')` = `2111-07-16`, so the first int is unlikely to be the number of days since 1900 – Andomar Jul 27 '12 at 12:31
  • is there another way of interpreting the date part, or maybe a different starting date, there has to be a combination between the two integers that will yield a datetime in july 2012 – user1458381 Jul 27 '12 at 12:43
  • Have a look at the code that does the inserts? – Andomar Jul 27 '12 at 12:44
  • Unfortunately that's off limits, I only have access to the database :) Thanks for the effort though. – user1458381 Jul 27 '12 at 12:47
  • Solved! Guess I didn't look hard enough but someone wiser pointed me in the right direction. http://stackoverflow.com/questions/10639677/clarion-date-conversion-d-date-add-subtract – user1458381 Jul 27 '12 at 13:54
  • Cheers, consider posting that as an answer here. – Andomar Jul 27 '12 at 14:14

1 Answers1

0

An integer value can not be converted direct to a date. You must convert to datetime like ->

 select cast(41535 as datetime)

Then to a date type ->

select cast(cast(41535 as datetime) as date)
Adrian
  • 671
  • 4
  • 17
  • 37