To get the first day of the specified date month, you can use the DateAdd function to subtract the number of days that have elapsed since the month of the specified date.
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Udf_firstdayofmonth]
(
@Date Date
)
RETURNS DATETIME
As
BEGIN
Return CAST (DATEADD (Day,1-day (@Date), @Date) as DATETIME)
End
Alternatively, use DateDiff to calculate the specified date and date at the beginning of a few months, then DateAdd plus the number of days apart, starting from scratch.
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Udf_firstdayofmonth]
(
@Date Date
)
RETURNS DATETIME
As
BEGIN
Return DATEADD (Month,datediff (month,0, @Date), 0)
End
Alternatively, the year or month is taken from the specified date, and then combined with 01 for the date, that is, the first day of the month of the specified date.
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Udf_firstdayofmonth]
(
@Date Date
)
RETURNS DATETIME
As
BEGIN
DECLARE @y NVARCHAR (4) = CAST (year (@Date) as NVARCHAR (4))
DECLARE @m NVARCHAR (2) = CAST (MONTH (@Date) as NVARCHAR (2))
Return CAST ((@y + n '-' + @m + n ' -01 ') as DATETIME)
End
Or, refer to this: http://www.jb51.net/article/23285.htm use the CONVERT function to specify the date format to convert, so you can also get the first day of the month that the specified date is in.
Copy Code code as follows:
CREATE FUNCTION [dbo]. [Udf_firstdayofmonth]
(
@Date Date
)
RETURNS DATETIME
As
BEGIN
DECLARE @ym NVARCHAR (+) = CONVERT (varchar (8), GETDATE (), 23)
Return CAST ((@ym + N ' ') as DATETIME)
End