March 18, 2011 at 8:39 am
My goal was to create a function that I could pass a datetype and date to and it would return a rounded version of the date.
Example: ROUNDDATE(m, '03/18/2011 10:28:00') would return 03/01/2011 00:00:00. I need to identifying the datepart property.
-- Conceptual function --
CREATE FUNCTION [dbo].[ROUNDDATE]
(
@DateType ?dateproperty?
@Date datetime
)
RETURNS datetime
AS
BEGIN
return dateadd(@DateType,datediff(@DateType,0,@Date),0)
END
Much like DATEADD( datepart , date ) and DATEDIFF ( datepart , startdate , enddate ).
Does anyone know what the datepart property is in the DateAdd / DateDiff function? Can I inherit or emulate it?
I had to settle for the following:
CREATE FUNCTION [dbo].[ROUNDDATE]
(
@Datetype varchar(2),
@Date datetime
)
RETURNS datetime
AS
BEGIN
declare @returndate datetime
set @returndate = getdate()
if @Datetype in ('year','yyyy','yy') set @returndate = dateadd(yyyy,datediff(yyyy,0,@Date),0)
if @Datetype in ('quarter', 'qq', 'q') set @returndate = dateadd(q,datediff(q,0,@Date),0)
if @Datetype in ('month', 'mm', 'm') set @returndate = dateadd(m,datediff(m,0,@Date),0)
if @Datetype in ('week', 'wk', 'ww') set @returndate = dateadd(wk,datediff(wk,0,@Date),0)
if @Datetype in ('day', 'dd','d') set @returndate = dateadd(dd,datediff(dd,0,@Date),0)
if @Datetype in ('hour','hh') set @returndate = dateadd(hh,datediff(hh,0,@Date),0)
if @Datetype in ('minute','n') set @returndate = dateadd(n,datediff(n,0,@Date),0)
if @Datetype in ('second','ss','s') set @returndate = @date
if @Datetype in ('millisecond','ms') set @returndate = @date
RETURN @returndate
END
March 18, 2011 at 8:43 am
What your desired output is for any date given, give the first day of the month @ midnight, correct?
-- Cory
March 18, 2011 at 8:47 am
Something like this should work.
DECLARE @date DATETIME
SET @date = GETDATE()
SELECT @date
SELECT DATEADD(dd, DATEDIFF(dd, 0, DATEADD(DD, 1 - DAY(@date), @date)), 0)
-- Cory
March 18, 2011 at 8:54 am
No my desire is to create an enterprise level function where I can pass a datepart and a datetime and have it return the datetime rounded to the datepart.
Example:
ROUNDDATE(d,'03/18/2011 10:50:42') --Result: 03/18/2011 00:00:00
ROUNDDATE(yyyy,'03/18/2011 10:50:42') --Result: 01/01/2011 00:00:00
ROUNDDATE(hh,'03/18/2011 10:50:42') --Result: 03/18/2011 10:00:00
I want to identify what SQL considers the datepart property.
March 18, 2011 at 9:30 am
DECLARE @DateType char(1)
DECLARE @Date datetime
SET @Date = GETDATE()
SET @DateType = 'H'
SELECT CASE @DateType
WHEN 'Y' THEN DATEADD(yy,datediff(yy,0,@date),0)
WHEN 'D' THEN DATEADD(d,datediff(d,0,@date),0)
WHEN 'H' THEN DATEADD(HH,datediff(HH,0,@date),0)
END
Jim
March 18, 2011 at 9:39 am
The Datepart argument of DATEADD cannot have a variable passed to it according to BOL:
http://msdn.microsoft.com/en-us/library/ms186819.aspx
datepart
Is the part of date to which an integer number is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
So, you'd have to use a case statement or similar to achieve this (or use dynamic SQL).
March 18, 2011 at 9:46 am
Thank you for your reply, Howard. I had read that document but I was hoping there was a work-around.
From my understanding, I cannot use Dynamic SQL because it cannot be called in a function.
March 18, 2011 at 9:50 am
True - a series of conditional statements isn't too arduous though as it's a relatively small list
March 18, 2011 at 9:57 am
If you're using SQL 2008 then its simple
Select CONVERT(DATETIME,CONVERT(DATE,GETDATE()),112)
This will automatically strip off the Time part of the date.
PS : The 112 is option, and an old habit of mine.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 18, 2011 at 10:27 am
Emote :BeatingHeadAgainstDesk:
March 18, 2011 at 10:54 am
ptn1 (3/18/2011)
Emote :BeatingHeadAgainstDesk:
:Sigh:
I get tired of all the blocked sites from work...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 18, 2011 at 7:15 pm
You may find that a Case structure beats a series of IF's (as suggested by Howard), and that using date arithmetic is faster than CONVERT().
Create FUNCTION [dbo].[ROUNDDATE]
(
@Datetype varchar(4),
@Date datetime
)
RETURNS datetime
AS
BEGIN
declare @returndate datetime
Select @returndate
= case when @Datetype in ('year','yyyy','yy')
then DateAdd(yy,Datediff(yy, 0, @date),0)
when @Datetype in ('quarter', 'qq', 'q')
then DateAdd(qq,Datediff(qq, 0, @date),0)
when @Datetype in ('month', 'mm', 'm')
then DateAdd(mm,Datediff(mm, 0, @date),0)
when @Datetype in ('week', 'wk', 'ww')
then DateAdd(ww,Datediff(ww, 0, @date),0)
when @Datetype in ('day', 'dd','d')
then DateAdd(dd,Datediff(dd, 0, @date),0)
when @Datetype in ('hour','hh')
then DateAdd(hh,Datediff(hh, 0, @date),0)
when @Datetype in ('minute','mi','n')
then DateAdd(mi,Datediff(mi, 0, @date),0)
Else @Date
End
RETURN @returndate
END
Note that the default result covers both the "seconds" and "milliseconds" options in the original code as well as user mis-entries like "min" for minute. One caveat is that the date arithmetic method is limited by the output of Datediff() being an INT, so it won't handle seconds or milliseconds for dates more than about half a century past the base date of 1 January 1900.
March 18, 2011 at 8:18 pm
Interesting, John. I'm always looking for ways to optimize queries. I appreciate your contribution.
March 18, 2011 at 10:12 pm
Makes me wish that SQL Server had the TRUNC function like Oracle, it is very handy for this purpose.
Someone even suggested it in Connect, http://connect.microsoft.com/SQLServer/feedback/details/483913/add-a-dateround-function-similar-to-oracles-trunc-for-date-handling, but it was turned down. I think it would be great to have, anyone want to re-file it and see if Microsoft is in a more giving mood? 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply