Last Saturday in month
-
Hi there,
Would somebody out there know how to calculate the last Saturday in any given month?
Regards,
David
-
Try this
SET DATEFIRST 1
DECLARE @DATE datetime DECLARE @EOM datetime DECLARE @SAT datetime
-- set a date to use SET @DATE = '15 Jul 2005' -- add a month SET @EOM = DATEADD(mm, 1, @DATE) -- subtract the days to get last day of month SET @EOM = DATEADD(dd, -DAY(@EOM), @EOM) -- subtract days using weekday number to get Saturday SET @SAT = DATEADD(dd, -(DATEPART(dw, @EOM))-1, @EOM) -- display the result SELECT @EOM as [EOM] , DATENAME(dw, @EOM) as [EOM_Day] , @SAT as [Last_Sat] , DATENAME(dw, @SAT) as [Last_day]
--------------------
Colt 45 - the original point and click interface
-
phillcart, your choice of date was perfect! The last day of that month is Sunday, hence you need a bit of flow control to prevent going back a full week to get the last Saturday...
SET DATEFIRST 1
DECLARE @DATE datetime,
@EOM datetime,
@SAT datetime
-- set a date to use
SET @DATE = '15 Jul 2005'
-- add a month
SET @EOM = DATEADD( mm, 1, @DATE)
-- subtract the days to get last day of month
SET @EOM = DATEADD( dd, -DAY( @EOM), @EOM)
-- subtract days using weekday number to get Saturday
IF DATENAME( dw, @EOM) = 'Sunday'
BEGIN
SET @SAT = DATEADD( dd, - 1, @EOM)
END
ELSE
BEGIN
SET @SAT = DATEADD( dd, -( DATEPART( dw, @EOM)) - 1, @EOM)
END
-- display the result
SELECT @EOM AS [EOM],
DATENAME( dw, @EOM) AS [EOM_Day],
@SAT AS [Last_Sat],
DATENAME( dw, @SAT) AS [Last_day]
I wasn't born stupid - I had to study.
-
I was thinking about that after I posted the code. Surely it could be done without the flow control. Some sort of logic involving the datepart value maybe ????
--------------------
Colt 45 - the original point and click interface -
Probably - but I figured a bulldozer approach was just what it needed...
(It probably could be done with a CASE statement...)
I wasn't born stupid - I had to study.
-
The following code makes it possible to get the last weekday for any year and month selected. You can fit this code into a stored procedure with month and year as input parameters.
DECLARE @month int, @year int, @day int, @date varchar(11), @lastWeekday varchar(11), @teller int
SET @month = 2 --// input in case of stored procedure
SET @year = 2002 --// input in case of stored procedure
SET @day = 22 --// standard, earliest possible last Saturday of month
SET @date = CONVERT(varchar(4), @year) + '-' + CONVERT(varchar(2),@month) + '-' + CONVERT(VARCHAR(2), @day)
SET @teller = 0 --// default value
WHILE @teller < DATEPART(dd,DATEADD(dd, -DAY( DATEADD( mm, 1, @date)), DATEADD( mm, 1, @date))) - 22 --number of days to endth of month
BEGIN
IF DATEPART(dw, @date) = 7 --// thus a Saturday
BEGIN
SET @lastWeekday = @date
END
SET @Teller = @Teller + 1
SET @day = @day + 1 --// next day
SET @date = CONVERT(varchar(4), @year) + '-' + CONVERT(varchar(2),@month) + '-' + CONVERT(VARCHAR(2), @day)
END
SELECT 'Last ' + DATENAME(dw,@lastWeekday) + ' of the selected year and month is on ' + CONVERT(VARCHAR(50), CONVERT(DATETIME,@lastWeekday),106)
-
I think it can be even simpler if you're not doing it inside UDF (UDF does not allow SET DATEFIRST). It will work for any valid dates, and you can specify whether you want last Saturday, last Sunday...etc. of the given month.
/*
@DW = day of week which to look for (1=Monday!!!)
@mydate = specifies the month and year (day must be entered, but result is the same regardless of which day it is)
*/
DECLARE @last_day datetime, @mydate datetime, @DW int
SET @DW = 6 /*Saturday*/
SET @mydate = '20051103' /*or GETDATE() or select date you need to work with from some table*/
/*calculate the last day of the same month*/
SET @last_day = dateadd(day, -1, dateadd(month, 1, convert(char(6), @mydate, 112) + '01'))
/*find the last specified day of week before last day (including last day)*/
SET DATEFIRST @DW
SELECT @last_day + (1-datepart(dw,@last_day))
EDIT : should be followed by re-setting the default datefirst value, e.g. SET DATEFIRST 1
-
SET DATEFIRST 1
DATEADD(month,DATEDIFF(month,0,@DATE)+1,0)
- 1
- (CAST(SUBSTRING('2345601',DATEPART(weekday,DATEADD(month,DATEDIFF(month,0,@DATE)+1,0)-1),1) as int))
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for the prompt replies,
The reason I ask it to do with daylight savings and GMT. I was looking to apply the following logic, and I believe what you have suggested will help:
If the date/time is between 16:59:59 on the last Saturday in March and 15:59:59 on the last Saturday in October then add 10:00 hours, otherwise add 11:00 hours.
Regards,
David
-
Dunno about all the DST and GMT stuff, but here's a way to find the last Saturday of a month given any date in that month... since it includes time, I imagine you could add the DST and GMT offsets before or after you run the following... no strings, either...
Also works for doing a whole column in a table... just sub the datetime column name for @Date in the SELECT...
SET DATEFIRST 7
DECLARE @Date DATETIME
SELECT @Date = '07/15/2005'
SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0)
-DATEPART(dw,DATEADD(mm,DATEDIFF(mm,0,@Date)+1,0))
Adam Mechanic would probably remind you about the need for auxilary calendar tables or at least a numbers table...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Calendar and numbers tables are the best approach! Do a search on these - RGR'us has a number of postings with those approaches...
I wasn't born stupid - I had to study.
-
Farrell,
You didn't read far enough down where I said "Adam Mechanic would probably remind you about the need for auxilary calendar tables or at least a numbers table...". Or were you just confirming what I said?
I also noticed that the solution you posted didn't use a numbers or calendar table nor even mentioned using them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Yeah, yeah,... I know. Do as I say, not as I do...
I wasn't born stupid - I had to study.
-
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply