August 26, 2009 at 12:18 am
Hi Folks,
How to get the Last Day of any month, .bUT FOR EVERY MONTH I SHOULD GETLast DAY
EXAMPLE; LastDay of the month August is 31 with their datename(week day)
LastDay of the month February is 28 with their datename(week day) ...likewise
August 26, 2009 at 1:03 am
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),
Case
datepart(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
Refer - http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/%5B/url%5D
September 1, 2009 at 5:23 am
Hi Folks,
Try this to get get last day of month
CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @vOutputDate DATETIME
SET @vOutputDate = CAST(FLOOR(CAST(@pInputDate AS DECIMAL(12, 5))) -
(DAY(@pInputDate) - 1) AS DATETIME)
SET @vOutputDate = DATEADD(DD, -1, DATEADD(M, 1, @vOutputDate))
RETURN @vOutputDate
END
GO
September 1, 2009 at 6:28 am
Much simplier:
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
You can find some more date routine here Some Common Date Routines
September 2, 2009 at 7:31 am
A small correction ...
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
September 3, 2009 at 7:13 am
Not sure if you're just trying to get the beginning and end of a month or the day that the month begins and ends on.
CREATE FUNCTION [dbo].[dbFunc_GetFirstDayOfMonth]
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
END
CREATE FUNCTION [dbo].[dbFunc_GetLastDayOfMonth]
(@LoadID AS Int)
RETURNS DATETIME
BEGIN
DECLARE @Year AS INT
DECLARE @Month AS INT
DECLARE @LastDay AS INT
SET @Year = LEFT(@LoadID, 4)
SET @Month = RIGHT(@LoadID, 2)
SET @LastDay = CASE @Month
WHEN 1 THEN 31
WHEN 2 THEN CASE WHEN @Year % 4 = 0 THEN 29 ELSE 28 END
WHEN 3 THEN 31
WHEN 4 THEN 30
WHEN 5 THEN 31
WHEN 6 THEN 30
WHEN 7 THEN 31
WHEN 8 THEN 31
WHEN 9 THEN 30
WHEN 10 THEN 31
WHEN 11 THEN 30
WHEN 12 THEN 31
END
RETURN CAST
(CASE WHEN LEN(@Month) = 1
THEN '0' + CAST(@Month AS CHAR(1)) ELSE
CAST(@Month AS CHAR(2)) END + '/' + CAST(@LastDay AS CHAR(2)) + '/' + CAST(@Year AS CHAR(4)) AS DATETIME)
END
September 3, 2009 at 9:00 am
I always used to handle the EndOfLastMonth problem by stripping the Month and Year out of the date as strings and using them to build the FirstOfNextMonth date, then using DATEADD to move back a day. It always felt a little clunky (and all the CASTs made the code less readable), so I like the much cleaner method proposed by Lynn (which is similar to what ps posted, as well).
However, I have one question. This method is contingent on the ability to cast the value 0 as a datetime = 01/01/1900, and -1 as 12/31/1899. Is this a stable standard (pardon the alliteration)? In other words, is it possible that MS could change something in the way they process dates that would cause this to break? I imagine using 0 and -1 would be safer than using 01/01/1900 and 12/31/1899, as 0 would represent Day 0, no matter what day that is. But I just wanted to check that this sort of casting is fairly conventional.
Just curious (since I'm planning on refactoring my FirstOfMonth and LastOfMonth functions to adopt this new method).
By the way, this also made me realize that I could strip the timestamp from any datetime by using this: dateadd(dd, datediff(dd, 0, @ThisDate), 0)
Again, this is much more streamlined than my string manipulation method.
So thanks for showing me the light.
September 3, 2009 at 9:23 am
Conversion of 0 or other numbers directly to datetime is documented in SQL Server Books Online.
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
Results:
DT FirstOfMonth LastOfMonth
----------------------- ----------------------- -----------------------
2009-09-03 11:23:58.503 2009-09-01 00:00:00.000 2009-09-30 00:00:00.000
2008-02-14 14:37:25.867 2008-02-01 00:00:00.000 2008-02-29 00:00:00.000
September 3, 2009 at 9:38 am
dmw (9/2/2009)
A small correction ...
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
Thanks. It took me a bit to find the "small" mistake.
September 3, 2009 at 10:52 am
Here's a simple scalar udf that gets the last day of month(LDOM). While the code is not "obvious" I don't consider the obtuse syntax a readability issue because it's well tested and commented in my environment-- and it's off in a udf.
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
BE AWARE udfs can dramatically slow your code if you are processing a large resultset. If you are doing this to set a page header or similar-- not a problem. But if you are SELECTing 10 million rows, each of which has a date and you are calling one or more udfs it may be time for a nap.
To get the day of the week "name" for SOMEDATE:
SELECT datename(weekday, SOMEDATE )
If you have to do this all in one step you could put both steps into another scalar udf (but then you'd need to collapse the date and the character day name into a string) or a stored procedure with 2 OUTPUT parms. But how you need to use this routine would dictate which if either of these is practical.
September 4, 2009 at 8:04 am
Try this
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
September 4, 2009 at 9:03 am
Here is some code for you all to play with. Use at your own risk. I tested this with my Tally table.
create function dbo.tvf_EOM (
@iDate datetime
)
returns table
as
return select
dateadd(mm, datediff(mm, 0, @iDate) + 1, -1) as EndOfThisMonth,
datename(dw,dateadd(mm, datediff(mm, 0, @iDate) + 1, -1)) as DatenameEndOfThisMonth;
go
select
dateadd(dd, t.N - 1, 0) as TheDate,
EndOfThisMonth,
DatenameEndOfThisMonth
from
dbo.Tally t
cross apply dbo.tvf_EOM(dateadd(dd, t.N - 1, 0))
where
t.N between datediff(dd, 0, getdate()) and datediff(dd, 0, getdate()) + 120;
September 4, 2009 at 3:50 pm
I have this page bookmarked because I can never remember how to get the last day of the month -- or some other date functions. It is very helpful:
http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/
September 4, 2009 at 10:01 pm
ps (8/26/2009)
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)),Case
datepart(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
Refer - http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/%5B/url%5D%5B/quote%5D
Heh... Ya just gotta love a 4 day work week. 😛 Also, there's no need for the CASE statement. For example,
SELECT DATENAME(dw, GETDATE())
I'll also add that I take exception to what's in the link you provided. Finding the first of the next month and subtracting 1 whole second means that you miss out on almost a second of information. It's a VERY bad way to do things.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2009 at 10:07 pm
lefrancisco1 (8/26/2009)
Hi Folks,How to get the Last Day of any month, .bUT FOR EVERY MONTH I SHOULD GETLast DAY
EXAMPLE; LastDay of the month August is 31 with their datename(week day)
LastDay of the month February is 28 with their datename(week day) ...likewise
Ummmm.... which year?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply