Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««89101112»»

Comparison of Dates in SQL Expand / Collapse
Author
Message
Posted Friday, May 01, 2009 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
Does the hack with FLOATs from DATETIMEs still work with DATETIME2?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708564
Posted Friday, May 01, 2009 10:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,569, Visits: 34,491
No. Nor the version that casts to int.

DECLARE @Today2 DATETIME2 = GETDATE()

SELECT CAST(FLOOR(CAST (@Today2 AS FLOAT)) AS DATETIME2)

Msg 529, Level 16, State 2, Line 5
Explicit conversion from data type datetime2 to float is not allowed.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #708597
Posted Friday, May 01, 2009 11:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:04 AM
Points: 1,384, Visits: 1,240
Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers...

I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/, i find a 60X increase in CPU time when you add the UDF...

Does that make sense? Can a UDF really be that expensive?

(just in case I did something stupid, here is the function):
CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #708628
Posted Friday, May 01, 2009 12:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 22,529, Visits: 30,298
Tao Klerks (5/1/2009)
Alright, alright, point taken - no more converting to float... now I need to go explain the dateadd/datediff thing to my developers...

I was hoping to be able to provide a nice UDF wrapper, but running Gail's test code from http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/, i find a 60X increase in CPU time when you add the UDF...

Does that make sense? Can a UDF really be that expensive?

(just in case I did something stupid, here is the function):
CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN
RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO



Just for S & G's, try this with a CROSS APPLY:
CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)
RETURNS table
AS
RETURN dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly
END
GO




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #708660
Posted Friday, May 01, 2009 12:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,569, Visits: 34,491
Tao Klerks (5/1/2009)
Does that make sense? Can a UDF really be that expensive?


Yes.

Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #708668
Posted Friday, May 01, 2009 1:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:04 AM
Points: 1,384, Visits: 1,240
Lynn Pettis (5/1/2009)
Just for S & G's, try this with a CROSS APPLY:


Sorry, I'm in an all-2000 environment right now. Testing is just a question of copying Gail's code from the link above and adding an extra case, however, if you're interested...


http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #708715
Posted Friday, May 01, 2009 1:21 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 11:04 AM
Points: 1,384, Visits: 1,240
GilaMonster (5/1/2009)

Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/


Do you have a ready blog article for every question ever raised?

I think I'm going to go print it all out and peruse over the weekend.



http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #708716
Posted Friday, May 01, 2009 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
Tao,

No; it just seems that way because the top N articles cover the top 80% of commonly-asked questions like this one about UDFs.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #708719
Posted Friday, May 01, 2009 1:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,569, Visits: 34,491
Tao Klerks (5/1/2009)
GilaMonster (5/1/2009)

Short answer. UDFs are not considered 'inline'. If they're run in a query, the run once for each row.
Long answer - http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/


Do you have a ready blog article for every question ever raised?



I was wondering if you'd think that.
Actually it's the other way around. I use questions here as ideas for blogs and I've seen a lot of problems with UDFs over time. Half the reason I write blog entries is so that I have something I can direct people to instead of having to write a couple pages every time common questions come up.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #708724
Posted Friday, May 01, 2009 1:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 22,529, Visits: 30,298
Okay, simple test, both returned the same actual execution plan.

Functions:
CREATE FUNCTION dbo.ufnDayOnly(@DateValue DateTime)
RETURNS table
AS
RETURN (select dateadd(dd, datediff(dd,0, @DateValue),0) as DateOnly )
GO

CREATE FUNCTION dbo.fn_DayOnly_DateTime (@DateValue DateTime)
RETURNS DateTime
WITH SCHEMABINDING
AS
BEGIN

RETURN dateadd(dd, datediff(dd,0, @DateValue),0)
END
GO

Test code:
create table dbo.LAPTest (
AccountID int,
Amount money,
Date datetime
);
go
create clustered index IX1_LAPTest on dbo.LAPTest (
AccountID asc,
Date asc
)
;
go

--===== Build the table 100 rows at a time to "mix things up"
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter < 1000000
BEGIN
--===== Add 100 rows to the test table
INSERT INTO dbo.LAPTest(
AccountID,
Amount,
Date)
SELECT TOP 100
AccountID =ABS(CHECKSUM(NEWID()))%50000+1,
Amount =CAST(CHECKSUM(NEWID())%10000 /100.0 AS MONEY),
Date = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
FROM
master.sys.columns t1
CROSS JOIN master.sys.columns t2
--===== Increment the counter
SET @Counter = @Counter + 100
END
;
go
select *, dbo.fn_DayOnly_DateTime (Date) from dbo.LAPTest;

select * from dbo.LAPTest cross apply dbo.ufnDayOnly(Date);

select *, dateadd(dd, datediff(dd,0, Date),0) as DateOnly from dbo.LAPTest;
go
drop table dbo.LAPTest;
go


Edit: Added a third query to the test code, but I haven't uploaded the third execution plan. Why, because it is identical to the other 2 I already uploaded.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)


  Post Attachments 
DateFunctionExecutionPlans.zip (2 views, 2.58 KB)
Post #708735
« Prev Topic | Next Topic »

Add to briefcase «««89101112»»

Permissions Expand / Collapse