Useful T-SQL Date functions
I was looking for a quick way to calculate the last day of the month in T-SQL and bumped into an article written by Gregory A. Larsen on DatabaseJournal.com. The article describes a number of T-SQL methods to calculate different date and time values.
I used information from that article to create a few user defined functions which I now use in my stored procedures. You can find the code for these functions in this post. Please link to the full article by Gregory A. Larsen for a full explanation and more date/time calculations.
Midnight for the Current Day
Using the code in the above article, I created a small user defined function that will give me a datetime value with the time set to 00:00:00. It makes it easier to check if a given datetime is on a specific date.
CREATE FUNCTION [dbo].[StripTimeFromDate] (@inputDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATEADD(d, DATEDIFF(d, 0, @inputDate), 0)
END
First Monday of the Month
I have a stored procedure that checks a number of things in the database. Please forgive me for not going into details here, but some data in these tables needs to be reset on the first Monday of the month. As I'm not allowed to create jobs on the server, I simply added the code to reset the information to the stored procedure. To calculate the first Monday of a month, I use the following user defined function.
CREATE FUNCTION [dbo].[FirstMondayOfMonth] (@inputDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATEADD(wk, DATEDIFF(wk, 0, dateadd(dd, 6 - datepart(day, @inputDate), @inputDate)), 0)
END
Last Day of the Month
Another one I now use is a user defined function to calculate the last day of the month. The application I now work on assigns tasks to users that need to be done on the first Monday of the month. When they complete the task, another task is assigned to take place on the last day of the month. The stored procedure that completes the task uses the following user defined function to calculate that date.
CREATE FUNCTION [dbo].[LastDayOfMonth] (@inputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN dateadd(ms, -3, DATEADD(mm, DATEDIFF(m, 0, @inputDate) + 1, 0))
END