last friday of a given month in sql server

sql-server tsql sql-date-functions
Abhishek Arora picture Abhishek Arora · Dec 21, 2012 · Viewed 11.6k times · Source

How do i get the date for last friday of the month in T-SQL?

I will be passing the year and month as parameter,e.g, 201211. If I pass '201211' as parameter it should return me '20121130' as answer as it's the date of last friday of month of november'12.

Answer

Ciarán picture Ciarán · Dec 21, 2012

The 5 January 1900 was a Friday. This uses that a base date and calculates the last Friday in any given month though you must give it a date during the month rather than just the month itself. Replace the 2012-12-01 in this with a date in your month

SELECT DATEADD(DY,DATEDIFF(DY,'1900-01-05',DATEADD(MM,DATEDIFF(MM,0,'2012-12-01'),30))/7*7,'1900-01-05')

You can also use this to get the last Saturday by replacing the 1900-01-05 WITH 1900-01-06 etc.

Related questions

First day of the next month

I'm trying get the results where it only displays OrderDates before the LAST day of the CURRENT month. I'm guessing it would be like this... SELECT OrderDate FROM Orders WHERE OrderDate < (code for first day of the next month?)

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More