SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),Casedatepart(dw, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))When 0 Then 'Sunday'when 1 then 'Monday'When 2 then 'Tuesday'when 3 Then 'Wednesday'When 4 Then 'Friday'When 5 Then 'Saturday'End as Day
declare @ThisDate datetime;set @ThisDate = getdate();select getdate() as CurrentDateTime, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) as BeginningOfNextMonth, -- Beginning of next month dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) as BeginningOfNextMonth -- End of this month
declare @ThisDate datetime;set @ThisDate = getdate();select getdate() as CurrentDateTime, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) as BeginningOfNextMonth, -- Beginning of next month dateadd(mm, datediff(mm, 0, @ThisDate) + 1, -1) as EndOfThisMonth -- End of this month
dateadd(dd, datediff(dd, 0, @ThisDate), 0)
select DT, FirstOfMonth = dateadd(mm,datediff(mm,0,a.DT),0), LastOfMonth = dateadd(mm,datediff(mm,-1,a.DT),-1)from ( --Test Data select DT = getdate() union all select DT = '20080214 14:37:25.867' ) a
DT FirstOfMonth LastOfMonth----------------------- ----------------------- -----------------------2009-09-03 11:23:58.503 2009-09-01 00:00:00.000 2009-09-30 00:00:00.0002008-02-14 14:37:25.867 2008-02-01 00:00:00.000 2008-02-29 00:00:00.000
CREATE FUNCTION [dbo].[udfLDOM] (@dd datetime) -- in: datetime out: LDOM (with time component stripped off)RETURNS datetime AS BEGIN RETURN dateadd(day,-1,dateadd(month,1,dateadd(month,datediff(month,0,@dd),0))) -- NOTE replace the line above with a modded version of the last line of code on the previous post; it does it in 2 function calls instead of my 4!END