May 21, 2009 at 2:53 am
Hi guys. I'm having some problems compiling a scalar function using the DATETIME2 datatype.
CREATE FUNCTION func_IncDatetime2
(
@toIncrease datetime
)
RETURNS datetime
AS
BEGIN
DECLARE @result datetime;
SELECT @result = @toIncrease + ((20.0)/(1440));
RETURN @result;
END
GOcompiles fine, butALTER FUNCTION func_IncDatetime2
(
@toIncrease datetime2
)
RETURNS datetime2
AS
BEGIN
DECLARE @result datetime2;
SELECT @result = @toIncrease + ((20.0)/(1440));
RETURN @result;
END
GOdoesn't compile. It returns the error message "Msg 206, Level 16, State 2, Procedure func_IncDatetime2, Line 16
Operand type clash: datetime2 is incompatible with numeric"
This seems rather odd as the following is sitting happily in a table declarationCREATE TABLE [dbo].[tbl_bob](
[dateTimeExpired] [datetime2](7) NOT NULL,
)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The "20.0" in the default value calculation of "(getdate()+(20.0)/(1440))" is 20 minutes.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl_bob', @level2type=N'COLUMN',@level2name=N'dateTimeExpired'
GO
ALTER TABLE [dbo].[tbl_bob] ADD CONSTRAINT [DF_tbl_bob_dateTimeExpired] DEFAULT (getdate()+(20.0)/(1440)) FOR [dateTimeExpired]
GO
I'm using DATETIME2 because BOL has this note on datetime
Note:
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.
What am I doing wrong guys?
May 21, 2009 at 3:22 am
Use the dateadd function instead... that works for both datetime and datetime2, e.g.:-
SELECT @result = DATEADD(minute,20,@toincrease)
The reason that your default constraint works is because you are adding 20 minutes to GETDATE(), which returns a datetime, and as you showed in your example, adding 20 minutes to a datetime works.
Why it doesn't work with a datetime2 must be down to the underlying datatypes Microsoft are using internally.
May 21, 2009 at 3:41 am
Thanks Ian. I've also changed the constraint declaration on the table.
May 21, 2009 at 3:50 am
Ian Scarlett (5/21/2009)
Why it doesn't work with a datetime2 must be down to the underlying datatypes Microsoft are using internally.
Datetime is internally a float and can implicitly convert to and from floats, ints, numerics, etc (which is what happens when you add a number to datetime). Datetime2 is not a float and does not convert implicitly to any of the numeric data types.
Adding numbers directly to a date is not documented to work. It's a quick hack that people have used because it has worked. It will not work with any of the new datetime data types. Use DATEADD, which is the documented and supported way to add periods to a date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2009 at 3:58 am
Thanks for that Gila. It's sometimes nice to know how things work in the background.
May 30, 2017 at 7:29 pm
It's a quick hack that people have used because it has worked.
It's not quite a hack.
All date-time manipulation a in Excel are built on this "hack".
It's rather an architectural approach:
Numerical (datetime) vs String with variable length (datetime2).
_____________
Code for TallyGenerator
May 31, 2017 at 3:42 am
Sergiy - Tuesday, May 30, 2017 7:29 PMIt's not quite a hack.All date-time manipulation a in Excel are built on this "hack".It's rather an architectural approach:Numerical (datetime) vs String with variable length (datetime2).
Datetime2 is not stored as a variable-length string, and you're replying to an 8 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2017 at 7:46 pm
GilaMonster - Thursday, May 21, 2009 3:50 AM
Ian Scarlett (5/21/2009)
Why it doesn't work with a datetime2 must be down to the underlying datatypes Microsoft are using internally.
Datetime is internally a float and can implicitly convert to and from floats, ints, numerics, etc (which is what happens when you add a number to datetime). Datetime2 is not a float and does not convert implicitly to any of the numeric data types. Adding numbers directly to a date is not documented to work. It's a quick hack that people have used because it has worked. It will not work with any of the new datetime data types. Use DATEADD, which is the documented and supported way to add periods to a date.
I, too, used to think that BOL was wrong about DATETIME being stored as 2 INTs because there was a huge amount of evidence it was actually a FLOAT. I was wrong and found out the hard way and still occasionally lick years-old wounds because of it.
Here's some "simple" proof in the form of code that DATETIME isn't stored as a float.
--===== If DATETIME were stored as a FLOAT, it would be able to withstand
-- a conversion to FLOAT and back to a DATETIME with no change.
-- This demonstrates that's not the case and that even a 38 place
-- DECIMAL conversion will not suffice.
DECLARE @DateTime DATETIME;
SELECT @DateTime = '00:00:11';
SELECT [DATETIME] = @DateTime
,[FLOAT AND BACK] = CAST(CAST(@DateTime AS FLOAT) AS DATETIME)
,[DECIMAL AND BACK] = CAST(CAST(@DateTime AS DECIMAL(38,38)) AS DATETIME)
;
The results tell it all.
DATETIME FLOAT AND BACK DECIMAL AND BACK
1900-01-01 00:00:11.000 1900-01-01 00:00:10.997 1900-01-01 00:00:10.997
Going a little deeper...
--===== A little deeper proof. You could verify the values stored by saving them in a table and
-- then using DBCC PAGE to confirm the values below are true (taking "little endian into consideration").
DECLARE @DateTime DATETIME;
SELECT @DateTime = '1900-01-11 12:00:00'
;
SELECT [BINARY] = CAST(@DateTime AS BINARY(8)) --This is what 10 and a half days after 1900-01-01 looks like
,[FLOAT] = CAST(@DateTime AS FLOAT) --Converted to float to prove it's 10 and a half days
,[10.5 as BIN] = CAST(10.5 AS BINARY(8)) --Converted to 10.5 to BINARY(8) and it's totally different.
;
Here's the output from that.
BINARY FLOAT 10.5 as BIN
------------------ ---------------------- ------------------
0x0000000A00C5C100 10.5 0x0301000169000000
Going one step further, let's take a closer look at that BINARY(8) conversion, which returns a result of 0x0000000A00C5C100. The following proves that DATETIME actually is stored as two 4 byte INTs, the first being the whole number of days since 1901-01-01 and the second is a representation of time as the number of 1/300ths of a second that have passed since midnight (which is the reason why the resolution of DATETIME is 3.3 milliseconds rounded to the nearest millisecond.
DatePart TimePart
0000000A 00C5C100 Just separated the BINARY(8) above with a space for readability
10 12,960,000 Converted to decimal
12,960,000 / 300 = 43,200 seconds = which is exactly half or .5 of an 86,400 second day.
What that also means is that the ability to do direct date math on the DATETIME data-type was anything but a hack. In fact, it was thoughtfully engineered to work that way, just like it works in an Excel spreadsheet and several other programs. IMHO, MS totally screwed the goose when they made the new temporal data-types unable to do direct date math. In fact, they screwed up so bad that they had to invent the DATEDIFF_BIG() function so that relational and SQL purists could actually calculate a duration using a start date/time and an end date/time without having to go to school for it. Using the DATETIME data-type, it's a simple subtraction and you simply need to CONVERT the result so that humans can read it. Like this...
Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)
Another trick that MS took away from us is...
PRINT '--===== This works just fine';
GO
DECLARE @NowDT DATETIME = GETDATE();
SELECT @NowDT = @NowDT + '14:35:09.217';
PRINT @NowDT;
GO
---------------------------------------------------------
PRINT '--===== This blows chunks of hot metal at you';
GO
DECLARE @NowDT2 DATETIME2(7) = GETDATE();
SELECT @NowDT2 = @NowDT2 + '14:35:09.217';
PRINT @NowDT2;
GO
Results:
--===== This works just fine
Jun 1 2017 12:43PM
--===== This blows chunks of hot metal at you
Msg 402, Level 16, State 1, Line 2
The data types datetime2 and varchar are incompatible in the add operator.
To play the old saw here, "Change is inevitable... change for the better is not."
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply