Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Last Day of any Month Expand / Collapse
Author
Message
Posted Wednesday, August 26, 2009 12:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 15, 2012 1:15 PM
Points: 14, Visits: 66
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
Post #777289
Posted Wednesday, August 26, 2009 1:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
 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
Post #777310
Posted Tuesday, September 1, 2009 5:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 11:50 PM
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




Post #780577
Posted Tuesday, September 1, 2009 6:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 22,986, Visits: 31,460
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



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)
Post #780627
Posted Wednesday, September 2, 2009 7:31 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, January 20, 2014 9:43 AM
Points: 670, Visits: 891
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

Post #781429
Posted Thursday, September 3, 2009 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:40 PM
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
Post #782133
Posted Thursday, September 3, 2009 9:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 290, Visits: 1,657
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.
Post #782264
Posted Thursday, September 3, 2009 9:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:37 PM
Points: 3,116, Visits: 11,385
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




Post #782290
Posted Thursday, September 3, 2009 9:38 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:59 AM
Points: 22,986, Visits: 31,460
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.



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)
Post #782305
Posted Thursday, September 3, 2009 10:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 436, Visits: 2,260
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
Post #782380
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse