Pages

Thursday, 21 October 2010

T SQL Functions

T SQL Function which returns the date for the previous month, the date is required in 2 digits

select dbo.wflastmonthdate(27)
result
2010-09-27 00:00:00

create FUNCTION [dbo].[wflastmonthdate] (@day int)
returns smalldatetime
as
BEGIN
RETURN

--select
cast (
convert(char(4),case when datepart(mm,getdate())=12 then datepart(yy,getdate())-1 else datepart(yy,getdate()) end)
+'-'+
CASE WHEN len (case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end) = 1 THEN
'0'+convert(char(1),case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end) ELSE
convert(char(2),case when datepart(mm,getdate())=12 then 1 else datepart(mm,getdate())-1 end)
END
+
'-'+convert(char(2),@day)+ ' 00:00:00.000'
as smalldatetime)

END

GO

No comments:

Post a Comment