Implicit int to datetime conversion

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Implicit int to datetime conversion

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-11 : 06:38:31

Why does this happen!??:


SELECT 1
UNION ALL
SELECT CAST('20010101' as datetime)

No error message, just converts 1 to a datetime. No idea why anyone would want this to be done implicitly. This is the behaviour I would expect:


SELECT 1
UNION ALL
SELECT CAST('20010101' as date)

This has caused quite an issue where a small typo should of raised an error message, but ended up messing up a lot of work. To explain a little further, when converting the datetime column like this back to a number in excel, the conversion is different and so slightly out. For example, the 1 above converts to 2. I don't really blame excel, I just don't know why that kind of conversion is implicit in SQL. Annoying. Any explanation why this happens would be nice just for my sanity.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-12 : 12:05:38

Is this the error you get?


Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-12 : 12:20:00

Hello and thanks for your reply. The issue is, I don't get an error and would expect to! I guess and easier way of writing this is:


DECLARE @datetest datetime=1

Why doesn't this fail? Why is 1 implicitly converted to datetime? Why would anyone use this? I would expect an error to be flagged up such as the one you showed and when running


DECLARE @datetest date=1

The behaviour shown for data type date is what I would expect for data type datetime and have no idea why this implicit conversion is carried out. Any reason why?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-12 : 14:20:21

datetime2????

Holycow...trying to compete with DB2..microseconds to 7 places...

How does that work? I thought it was based on the cpu clock....

Oh, look up everything in Books online btw

Ahhh..and they threw out their (insane) limitation that time didn't (accuratley) exist before January 1, 1753.

The thing is datetime is stored as 2 int columns internally...I don't know the storage of these new types

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-12 : 14:48:55

SQL Server converts the number 0 to 1900-01-01 datetime, 1 to 1900-01-02 datetime, 3 to 1900-01-01 datetime, -1 to 1899-12-31 datetime, and so on. This behavior is documented in SQL Server books online.

SQL Server 2008 does not support conversion of integers directly to date or datetime2.

As for why Excel gives you a different result: That is a legacy of Microsoft choosing to duplicate a bug in Lotus 123 in order to maintain compatibility. Excel accepts 1900-02-29 as a valid date, even though the year 1900 is not a leap year.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-12 : 16:44:49

With Excel 2010, the day zero has also changed from 1899-12-30 which was the base date (0) before.
Before 1900-01-01 in Excel was day 2, now it is day 1.

N 56°04'39.26"
E 12°55'05.63"

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-13 : 05:49:33

Ah, the excel thing makes more sense now. Thanks for that explanation. Still don't know why anyone would want an int to datetime conversion done implicitly, but I guess some people out there do otherwise they wouldn't include it. Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-13 : 09:26:34

The implict int to datetime conversion has been in place for many versions, and it would certainly break a lot of code if it was changed (most of the datetime scripts I've posted in the Script Library forum).

For example, this is a fairly standard way of removing the time part of a datetime value to get the datetime as of 00:00:00.000:

select dateadd(dd,datediff(dd,0,getdate()),0)

CODO ERGO SUM

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-13 : 09:42:35

Oh yeah, I even have one myself that I use:


CREATE FUNCTION [dbo].[Date] (@Year smallint,@Month tinyint,@Day tinyint)
RETURNS DateTime
AS
BEGIN
	RETURN Dateadd(month,(12*@Year)-22801+@Month,@Day-1)
END

Copy and pasted blindly without thinking too long how it works!

I feel after making my mistake it be nice to have an option to force more strict data conversions, so that the function above would have to be modified to:


CREATE FUNCTION [dbo].[Date] (@Year smallint,@Month tinyint,@Day tinyint)
RETURNS DateTime
AS
BEGIN
	RETURN Dateadd(month,(12*@Year)-22801+@Month,CAST(@Day-1 as datetime))
END

Well thanks for showing where it's used, that's some good information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources