create function [dbo].[fnMonth] (@dt datetime)
returns char(7)
as
begin
return convert(char(4), year(@dt)) + '-' + substring(convert(char(3), month(@dt) + 100), 2, 2)
end
create function [dbo].[fnYear] (@dt datetime)
returns char(4)
as
begin
return convert(char(4), year(@dt))
end
CREATE FUNCTION [dbo].[fnFirstDate] (@std_dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @first_dt CHAR(10)
SET @first_dt = CONVERT(CHAR(4), YEAR(@std_dt)) + '-' +
SUBSTRING(CONVERT(CHAR(3), MONTH(@std_dt) + 100), 2, 2) + '-' +
'01'
RETURN CONVERT(DATETIME, @first_dt)
END
-- select [dbo].fnLastDate
CREATE FUNCTION [dbo].[fnLastDate] (@std_dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, DATEADD(DAY, -1, DATEADD( MONTH, 1, dbo.fnFirstDate(@std_dt) )),120)
END