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

DateFloor Function Expand / Collapse
Author
Message
Posted Tuesday, November 16, 2010 10:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
Comments posted to this topic are about the item DateFloor Function
Post #1021921
Posted Wednesday, November 17, 2010 12:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 17, 2014 5:34 AM
Points: 4, Visits: 88
Won't the query cost for

select dbo.datefloor(getdate(),'dd')

be a lot higher than say

select convert(datetime, convert(char(10), getdate(), 120))

?
Post #1021939
Posted Wednesday, November 17, 2010 6:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 7:50 AM
Points: 26, Visits: 120
Yes, you will have a scalar computation versus a datatype conversion - slightly more expensive. This was more for load functions where rounding down to the nearest hour or minute was the goal, or trimming miliseconds (as developers will often ask). Cast/convert date to string is probably still recommended for rounding down to the nearest day. This is more of a pattern to round down to other parts. The cost is realized in CPU at load, and the benefit is realized in post-processing when doing aggregations to the sub-day grain without further scalar computations at report time.
Post #1022102
Posted Thursday, December 29, 2011 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 29, 2011 12:03 PM
Points: 4, Visits: 3
This is not the best way to implement this -- See http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server

declare @datetime datetime;
set @datetime = getdate();
select @datetime;
select dateadd(year,datediff(year,0,@datetime),0);
select dateadd(month,datediff(month,0,@datetime),0);
select dateadd(day,datediff(day,0,@datetime),0);
select dateadd(hour,datediff(hour,0,@datetime),0);
select dateadd(minute,datediff(minute,0,@datetime),0);
select dateadd(second,datediff(second,'2000-01-01',@datetime),'2000-01-01');

or converted to this function:

create function dbo.DATEFLOOR (
@seed datetime
, @part varchar(2)
)
returns datetime
as
begin

/*
Sample Usage (uses standard dateparts):
select 'second', dbo.datefloor(getdate(),'ss') union all
select 'minute', dbo.datefloor(getdate(),'mi') union all
select 'hour' , dbo.datefloor(getdate(),'hh') union all
select 'day' , dbo.datefloor(getdate(),'dd') union all
select 'month' , dbo.datefloor(getdate(),'mm') union all
select 'year' , dbo.datefloor(getdate(),'yy')
*/

select @retDate = case
when @part = 'ss' then dateadd(second,datediff(second,'2000-01-01',@seed),'2000-01-01')
when @part = 'mi' then dateadd(minute,datediff(minute,0,@seed),0)
when @part = 'hh' then dateadd(hour,datediff(hour,0,@seed),0)
when @part = 'dd' then dateadd(day,datediff(day,0,@seed),0)
when @part = 'mm' then dateadd(month,datediff(month,0,@seed),0)
when @part = 'yy' then dateadd(year,datediff(year,0,@seed),0)
end

return @retDate
end

Post #1227990
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse