CREATE FUNCTION dbo.DateSerial
(
@year int ,
@month int ,
@day bigint
)
RETURNS datetime
AS
BEGIN
DECLARE @date datetime
-- catch invalid year entries and default appropriately
SET @year =
CASE WHEN @year < 1900 THEN 1900
WHEN @year > 9999 THEN year(getdate())
ELSE @year
END
-- convert date by adding together like yyyymmdd
SET @date = cast(@year * 10000 + 101 AS char(8))
;
-- Add to date the proper months subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(mm , @month - 1 , @date)
-- Add to date the proper days subtracting 1,
-- since we used 1 as start instead of zero.
SET @date = dateadd(dd , @day - 1 , @date)
;
RETURN @date ;
END
;
Here is the usage:
SELECT dbo.DateSerial(2009, 4, 1), dbo.DateSerial(2009, -1, 1)
;
SELECT
WorkOrder ,
PostYear ,
PostMonth ,
RunTimeHours
FROM
WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE (
(YEAR(GETDATE())-PostYear)*12
+ ( MONTH(GETDATE()) - PostMonth )
) BETWEEN 0 AND 3
;
SELECT
WorkOrder ,
PostYear ,
PostMonth ,
RunTimeHours
FROM
WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE
DATEDIFF(MONTH ,
dbo.DateSerial(PostYear , PostMonth , 1),
GETDATE()) BETWEEN 0 AND 3)
;
Notice we were able to use standard date function as our DateSerial() tool returns a valid datetime value.
SELECT
WorkOrder ,
PostYear ,
PostMonth ,
RunTimeHours
FROM
WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE
dbo.DateSerial(PostYear , PostMonth , 1) >=
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
AND dbo.DateSerial(PostYear , PostMonth , 1) <
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)
;
Very useful.
SELECT dbo.DateSerial(YEAR(GETDATE()), MONTH(GETDATE()), 1 - 35)
;
This will return the date 35 days prior to the first day of the current month in the current year!
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:Date and Time Functions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186724.aspx
As you can see in SQL 2008 and higher, you now have Date and DateTime2 which support a broader range of valid dates. Still no DateSerial function, so this is still valid.
Thanks again for reading and voting above.
Kevin
Commented:
WHERE (PostYear = YEAR(GETDATE()) AND --current year
PostMonth BETWEEN MONTH(GETDATE()) - 3 AND MONTH(GETDATE()))
OR (MONTH(GETDATE()) < 4 AND ( --prior year, if applicable
PostYear = YEAR(GETDATE()) - 1 AND
PostMonth BETWEEN MONTH(DATEADD(MONTH, -3, GETDATE())) AND 12))