Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denfer
Starting Member
12 Posts |
 Posted - 2009-04-27 : 05:02:43
|
| HiI get serial date from Excel file that give me 2 days shift when I convert it on datetime formatWhen I open the Excel file it display 23-Feb-09 that correspond to the 39867 serial Date (right click -> format cell -> general)In SQL I did a little test using below code:-----DECLARE @RFFDate INTSET @RFFDate = 39448SELECT DATEADD(dd, @RFFDate, '1/1/1900')-----Output -> 2009-02-25 00:00:00.000On what I read Excel can have Either 1900 or 1904 origin date but here it seems to be 12/30/1899Do someone already have this issue ?How can I get the correct date ?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-27 : 05:14:12
|
Excel for Macintosh has 1904-01-01 as base date, Excel for PC has 1899-12-30 as base date.Use DATEADD(DAY, -2, ExcelCellValue)
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
denfer
Starting Member
12 Posts |
Posted - 2009-04-27 : 05:19:27
|
| Thanks PesoWhere did you get this information As all I get is that Excel use the 1/1/1900 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-27 : 05:22:02
|
Experience. I have worked with Excel, both for PC and Mac, since version 1.0
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-27 : 05:23:27
|
And here are some links to back up my statements.http://support.microsoft.com/kb/180162http://support.microsoft.com/default.aspx/kb/303216
E 12°55'05.63"N 56°04'39.26" |
|
|
|
denfer
Starting Member
12 Posts |
Posted - 2009-04-27 : 06:13:33
|
| Thanks for your help |
|
|
|
|
|
|
convert Excel Serial Date