TSQL Functions - First and last day of month.

As outlined in a previous post located here.
I discussed needing to group by day and needed a simple in line function to set the current date to midnight. The next step was to allow grouping on the month. I needed to create function(s) that allow me to set a date to the first (or last) day of the month that is part of the date. Hre's what I came up with:

Create Function [dbo].[udf_DateTimeToFirstOfMonth] (@inputdate)
returns datetime
Begin
return DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0)
End;
go

A quick look at the code reveals a familiar call to datediff for day 0 (zero) AKA 1900-01-01. This code gets the number of months since 1900-01-01 and adds then to 1900-01-01. This will give us the first day of the inputdate month.

I also needed the ability to set the dates to the last day of the input month. Here's what I came up with:

create function [dbo].[udf_DateTimeToEndOfMonth](@inputdate datetime)
returns datetime
as
begin
return DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @DateAndTime), 0)))
end

This function adds a month and subtracts a day from that date. Pretty straight forward stuff. Of course I would need a way to set dates to the first and last of the week, month, quarter, year, etc... So here they all are. Please note that some of the functions are nested.


create function [dbo].[udf_DatetimeToFirstOfQuarter](@Date datetime)
returns datetime
as
begin
declare @QuarterDate datetime
set @QuarterDate = dateadd(month, (ceiling(month(@date) / 3.0) - 1) * 3, dbo.udf_DatetimeToFirstOfYear(@Date))
return @QuarterDate
end
go

create function [dbo].[udf_DatetimeToEndOfQuarter]
(@Date datetime)
returns datetime
as
begin
declare @QuarterDate datetime

set @QuarterDate = dateadd(day, -1, dateadd(month, ceiling(month(@Date) / 3.0) * 3, dbo.udf_DatetimeToFirstOfYear(@Date)))

return @QuarterDate
end
go

create function [dbo].[udf_DateTimeToFirstOfYear](@DateAndTime datetime)
returns datetime
as
begin
return DATEADD(YEAR, DATEDIFF(YEAR, 0, @DateAndTime), 0)
end
GO

create function [dbo].[udf_DateTimeToEndOfYear]
(
@DateAndTime datetime
)
returns datetime
as
begin
return DATEADD(DAY, -1, DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @DateAndTime), 0)))
end
GO

create function [dbo].[udf_DatetimeToFirstOfWeek](@Date datetime)
returns datetime
as
begin
declare @Result datetime
set @Result = dateadd(day, datediff(day, '1/1/1753', @Date) / 7 * 7 - 1, '1/1/1753')
return @Result
end
go

create function [dbo].[udf_DatetimeToQuarter](@Date datetime)
returns char(4)
as
begin
declare @Quarter char(4)

select @Quarter = case
when month(@Date) > 9 then 'Q1' + right(cast(year(@Date) + 1 as varchar(4)), 2)
when month(@Date) > 6 then 'Q4' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) > 3 then 'Q3' + right(cast(year(@Date) as varchar(4)), 2)
else 'Q2' + right(cast(year(@Date) as varchar(4)), 2)
end

return @Quarter
end
go

create function [dbo].[udf_DatetimeToFiscalQuarter](@Date datetime)
returns char(4)
as
begin
declare @Quarter char(4)

select @Quarter = case
when month(@Date) >= '7' then 'Q1' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) >= '1' then 'Q2' + right(cast(year(@Date) as varchar(4)), 2)
when month(@Date) >= '3' then 'Q3' + right(cast(year(@Date) as varchar(4)), 2)
else 'Q4' + cast(year(@Date) as varchar(4))
end

return @Quarter
end

GO

Good luck with dates!