Link to home
Start Free TrialLog in
Avatar of Craig Lambie
Craig LambieFlag for Australia

asked on

CAST as int DateTime - Wrong Date in Excel? Add 1 or 2 days to Serial depending on Morning/ Afternoon?

Hi Experts,

I am using some SQL in an ASP output to Excel.
In order for the DateTime to appear correctly in Excel, as a Date Field I am converting the date to a serial number and putting it as the xnum value in the Document Map for the Excel Output in HTML.

To get the Serial of the DateTime in in the SQL output I am using

CAST (sdtDateField as int)

This proves to be 2 days out, as you can see below in the T-SQL and Results....
Unless it is after Midnight, then it is only 1 day out?

Any Ideas on how to deal with this correctly?
I can only think to write add one day in the afternoon and 2 in the morning?

Thanks


-- Excel Today = 39514
 
PRINT DATEPART(yyyy, GETDATE())
PRINT DATEPART(mm, GETDATE())
PRINT DATEPART(dd, GETDATE())
 
DECLARE @vchDate varchar(11)
 
SET @vchDate = DATEPART(yyyy, GETDATE()) + '-' + DATEPART(mm, GETDATE()) + '-' + DATEPART(dd, GETDATE()) 
 
PRINT CAST ( @vchDate as int)
PRINT CAST(GETDATE() as int)
PRINT CAST(DATEADD(hh, 2,GETDATE()) as int)
 
 
===== Results  ran at 2008-03-07 10:55:00 ==============
2008
3
7
2018
39512
39513

Open in new window

Avatar of Kobe_Lenjou
Kobe_Lenjou
Flag of Belgium image

Have you accounted for all Timezone / DST differences?

Maybe Excell has a different epoch the SQL server.

You can get the XLS epoch by using 0 as serial.
Avatar of Craig Lambie

ASKER

Excel is returning
00/01/1900

when I have it set to 0

SQL Server
DECLARE @vchDate datetime
SET @vchDate = '1900-01-01 00:00:00'
PRINT CAST ( @vchDate as int)
Result = 0

DECLARE @vchDate datetime
SET @vchDate = '1900-01-01 13:00:00'
PRINT CAST ( @vchDate as int)
Result = 1

Excel Results
01/01/1900 00:00
1

01/01/1900 13:00
1.541666667
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think that works??




SET @vchDate = '1900-01-01 00:00:00'

PRINT CAST ( @vchDate as int)
PRINT FLOOR(CAST ( @vchDate as float)) + 1

Result =
0
1

SET @vchDate = '1900-01-01 13:00:00'

PRINT CAST ( @vchDate as int)
PRINT FLOOR(CAST ( @vchDate as float)) + 1

Result =
1
1

I don't understand, but it works....

Thanks!
casting the date as int automatically rounds off the value. So the code converts it first to float then use floor to always round down the value.