asked on

SQL Date Integer value different from other programs by 2 days

If I convert a date to an integer in excel or sharepoint I get the same value. For example, 3/10/03 converts to 37960. If I convert that same date to an integer in SQL 2005 I get 37958. What is causing the difference?

reb73

The base date in SQL Server is 01-Jan-1900 while in VBA the base date is 30-Dec-1899 for some reason and this two days is the difference.. You can verify this using

select cast(0 as datetime) -- TSQL will yield 01-Jan-1900

?CDate(0) -- VBA/VBScript will yield 30-Dec-1899

You can either use 2 as an offset or just deal with literal dates/native datetime datatype variables in each case..

ASKER CERTIFIED SOLUTION

TextReport

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rkanter

ASKER

reb73 - thanks for the information. When I googled 'date integer values' they alll seem to indicate that SQL uses the same start date.
Textreport- the problem is I am comparting a value in sharepoint to a value in SQL. If I just do a date comparison it fails. If I pad it with a couple of days it works.

TextReport

When you are comparing the value in SharePoint and SQL Server what method are you using to retrieve the data from both Sharepoint and SQL Server, there should be a way to retrieve the values as native dates rather than integers.

Finally please note the difference in EXCEL with the value in the spreadsheet vs the value in a cell and that the 1904 date option only affects the spreadsheet and not VBA.

Cheers, Andrew

rkanter

ASKER

Orginally I was just comparing the valies, select .... where sharepointdate = sqldate.... When it failed I decided to just query the values. Sharepoint gave me the integer value and sql gave me a date. I tried to convert the integer value of sharepoing to a date, but it is off by two days from how it appears on sharepoint. If I just subtract 2 that everything works, which it sounds like is the easiet solution. I would just hate for this to fail again after MS decided to change the start date in some service pack

TextReport

Sorry but I don't know enough about sharepoint to comment on that side, working between Access or Excel and SQL then the way you query the data takes starting date into account.
Cheers, Andrew