Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Day of any Month


Last Day of any Month

Author
Message
lefrancisco1
lefrancisco1
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 79
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
ps.
ps.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2455 Visits: 3668
 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/



Pradeep Singh
akilamm
akilamm
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 46
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
Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27355 Visits: 38151
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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Martin Wills
Martin Wills
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 893
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


Janie.Carlisle
Janie.Carlisle
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 86
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
Steve Thompson
Steve Thompson
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 2076
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.
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3922 Visits: 11771
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


Lynn Pettis
Lynn Pettis
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27355 Visits: 38151
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
mstjean
mstjean
SSC-Addicted
SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)SSC-Addicted (493 reputation)

Group: General Forum Members
Points: 493 Visits: 2542
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.


Cursors are useful if you don't know SQL
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search