How to find a last friday of each month

Question

0.00/5 (No votes)

See more:

SQL

Hi,
Please help me to find the last friday of each month using SQL

Posted 22-Feb-12 14:48pm

hasithakaru

Add a Solution

3 solutions

Solution 3

Try this one.

SQL

declare @year int
set @year =2011

-- First and Last Friday
select min(dates) as [First Friday], max(dates) as [Last Friday] from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type='p' and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)='friday'
group by DATEADD(month,datediff(month,0,dates),0)

Best Regards,
@iamsupergrasya

Posted 22-Feb-12 17:46pm

graciax8

Solution 1

Here is tips for you:
- Find number of days in month.
- Loop step -1 from number of day in month until match Friday.
If you use SQL Server, to find Friday:

SQL

Datepart(weekday,@YourDate)=6

Posted 22-Feb-12 15:10pm

Bun Leap_kh

Comments

hasithakaru 22-Feb-12 21:47pm

Hi,
Can you explain it further.The question is as follow.

7. Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month).

ENAME HIREDATE FIRSTPAYD
---------- --------- ---------
SMITH 17-DEC-80 26-DEC-80
ALLEN 20-FEB-81 27-FEB-81
WARD 22-FEB-81 27-FEB-81
JONES 02-APR-81 24-APR-81
MARTIN 28-SEP-81 25-SEP-81
BLAKE 01-MAY-81 29-MAY-81
CLARK 09-JUN-81 26-JUN-81
SCOTT 09-DEC-82 31-DEC-82
KING 17-NOV-81 27-NOV-81
TURNER 08-SEP-81 25-SEP-81
ADAMS 12-JAN-83 28-JAN-83
JAMES 03-DEC-81 25-DEC-81
FORD 03-DEC-81 25-DEC-81
MILLER 23-JAN-82 29-JAN-82

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dhol Gaurav · Accepted Answer · 2012-02-22T17:07:00

You can find last Friday from the below Query, i took a dummy day, you can done with the table, where ever you wants.

SQL

DECLARE @dateCol datetime
SELECT @dateCol = GetDate() + 60

SELECT LastWeekDay 
  FROM (
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-1 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-2 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-3 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-4 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-5 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-6 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-7 AS LastWeekDay
) AS YourFridayTable
WHERE DATENAME(WeekDay,LastWeekDay) = 'Friday'