Converting integer to datetime

Question

Post reply

Converting integer to datetime

Mark van der Bend

Old Hand

Points: 324
More actions
December 8, 2006 at 1:31 am

#115523

Hi there,
When I convert an integer to a datevalue in Excel, the returned datevalue differs from the datevalue I get when I do the same in SQL Server 2005 (and Pervasive.SQL). Here's an example: I know for sure that 38718 is 1/1/2006. Excel agrees on me. But if I run the following SQL statement in SQL Server I get 1/3/2006. Why?
SELECT
CAST(38718 as DateTime) AS NewDate
returns
NewDate
-----------------------
2006-01-03 00:00:00.000
(1 row(s) affected)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

"Zero" date in VB is 1899-12-30, "Zero" date in SQL Server is 1900-01-01.

Excel has VB on background.

_____________
Code for TallyGenerator

Mark van der Bend Old Hand Points: 324 More actions · Answer 2

Thanx. I now know the exact difference. VB starts at 1 and SQL starts at 0. But there's yet another strange difference. Note that in SQL Server february 1900 has 28 days, and in VB 29 days. So that's why they're two days off.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

VB is wrong. 1900 was not a leap year, so there should not be a 1900-02-29.

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 4

1900 is a leap year. All years divisible by 4 is a leap year. Right? Or did I miss something?

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"

Sergiy SSC Guru Points: 110208 More actions · Answer 5

1700, 1800, 1900 were not leap years.

2100, 2200 will not be as well.

2000 was an exclusion because of "reverse compesation".

Leap year adds 6 hours to each year. Instead of 5 hours + don't remember how many minutes. Every 100 years it brings extra day which is compensated by making "00" years not leap. But every 2000 years it brings one missing day which was compensated by making year 2000 a leap one.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

The rule for leap years is...

Any year evenly divisible by 4 unless it is evenly divisible by 100 in which case it must also be evenly divisible by 400 to be a leap year. It was the oversight of the last part (400) of that rule that was one of the causes of the Y2K fiasco.

http://www.google.com/search?hl=en&q=DEFINITION+OF+%22LEAP+YEAR%22&btnG=Google+Search

http://www.answers.com/topic/leap-year

http://www.computeruser.com/resources/dictionary/definition.html?lookup=7281

http://www.nmm.ac.uk/server/show/conWebDoc.349

...etc...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

P.S.

I hope to live long enough to see what happens when the pants fall off of the SmallDateTime datatype on 2079-06-07 00:00:00.000

And why the heck are you passing integers as dates between systems? As you have found out, it's a sure fire way to not get the correct date

And, as Lynn said... VB is flat out wrong... there is no 19000229... so is MS-Excel (version 97 and 2003 are, anyway) and a couple of other wonderful MS products.

Pass date strings between apps using the ISO format (yyyymmdd) or you're just begging for late deliveries, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

When you pray for long life ask nt only for yourself. Pray also for Microsoft stil to be there and support current version of MS SQL Server.

_____________
Code for TallyGenerator

Mark van der Bend Old Hand Points: 324 More actions · Answer 9

I have to pas integers as dates because I'm converting from the Greforian style. So, 732312 equals to 1/1/2006. I have to subtract 1900*365.25+14+365 to get the datevalue (integer) in any other date format. I used to do this in Access, but I converted my app to C# and SQL Server and I noticed the difference in the outcome of the dateconversion (fortnunately in time).

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 10

"Greforian?" Do you mean "Gregorian" ? Do you have a link that explains why 732312 equals to 1/1/2006? Might be able to pull a rabbit out of the hat if I knew...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)