Web design and hosting, database, cloud and social media solutions that deliver real business results
  • Database Services
    • Technical Articles
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
    • Technical Tools
      • Claytabase Server Disk IOPs Calculator
      • SQL Code Backup
      • SQL Printer
      • Stored Procedure Field Generator
  • Web Services
    • Logo Design
    • Open Projects
      • Site Management Project
    • Web Site Optimisation
      • Blog Micro-Site or Landing Page?
      • Keeping Content Natural
      • How do I minimise HTML?
      • Should I use a CDN?
      • Should I use HTTPS?
      • What is SEO?
    • Website Security
      • Disabling old TLS Protocols in IIS
    • Technical Articles
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Technical Tools
      • Colour Picker
      • CSS Optimiser
      • JavaScript Optimiser
      • Lightbox
  • Cloud Services
    • Microsoft Azure
    • Microsoft Office
    • Technical Articles
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
      • Task Scheduler Error 2147943645
  • Social Media Services
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About
    • Blog
      • Building Ousia Content Management System
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
    • Portfolio
    • Team
      • Chester Copperpot
      • Gavin Clayton
CS - češtinaDE - DeutschES - EspañolFR - FrançaisHI - हिंदीIT - italianoPL - polskiPT - PortuguêsRU - русскийTR - TürkZH - 中国的

Function to Return Specific Day of Month

SQL Server 2008, Function to Return Specific Day of Month
Using SQL Server Date Functions

This has been useful for our maintenance plan, as we can use one script and add code that will run on a specific day (like the last Sunday, or 1st Weekday), I have also used it for forecasting the times that Jobs will run on our servers.

The function only requires three inputs, the month you are interested in, the type, and the number.

It uses similar information as Microsoft use for monthly job schedules.

more: SQL Server flexible date Functions and calendar Table

SQL

CREATE FUNCTION dbo.GetMonthDay(@Month DATETIME,@Type INT, @Counter INT) RETURNS DATETIME AS BEGIN

SET @Month = CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@Month),@Month))

DECLARE @CurDate DATETIME=@Month,@Date DATETIME,@Matches INT=0,@TempDate DATETIME

WHILE @CurDate

IF (SELECT (CASE @Type WHEN 1 THEN (CASE WHEN DATEPART(WeekDay,@CurDate)=1 THEN 1 ELSE 0 END)

WHEN 2 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=2 THEN 1 ELSE 0 END)

WHEN 3 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=3 THEN 1 ELSE 0 END)

WHEN 4 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=4 THEN 1 ELSE 0 END)

WHEN 5 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=5 THEN 1 ELSE 0 END)

WHEN 6 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=6 THEN 1 ELSE 0 END)

WHEN 7 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=7 THEN 1 ELSE 0 END)

WHEN 8 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,2,3,4,5,6,7) THEN 1 ELSE 0 END)

WHEN 9 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (2,3,4,5,6) THEN 1 ELSE 0 END)

WHEN 10 THEN (CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,7) THEN 1 ELSE 0 END)

ELSE 0 END))=1 BEGIN

SET @Matches = @Matches+1

SET @TempDate = @CurDate

END

IF @Matches=@Counter AND @Counter<32 BEGIN

SET @Date=@TempDate

END

SET @CurDate=DATEADD(DAY,1,@CurDate)

END

RETURN ISNULL(@Date,@TempDate)

END

GO

What it does

The values we have are similar to the sysschedules table but I will list them here;

@Type

  • 1=Sunday
  • 2=Monday
  • 3=Tuesday
  • 4=Wednesday
  • 5=Thursday
  • 6=Friday
  • 7=Saturday
  • 8=any day up to the counter or last day of the month
  • 9=any weekday up to the counter or the last day of the month
  • 10=any Saturday or Sunday up to the counter or the last occurrence of the month

@Counter

  • 1=1st
  • 2=2nd
  • 3=3rd
  • 4=4th
  • 32=Last (these are slightly different)

The steps it follows are as follows;

  • Set the input date to the first day of the month
  • Declare variables for our loop through the month
  • Loop through each date and update out match count if there is a match, and update a temporary date to hold the last match
  • If the matches equal our counter, and the counter is less than 5 (5=last day) then set our return date as the temp date, this will then exit the loop as the date is no longer null.
  • Add one to our current date and perform our check again.
  • Return our date, or if null the temp date which was our last match.

Below is an example of it in use. This will run every Sunday of February.

You can use it in a IF clause to determine whether or not to run certain parts of a stored procedure.

Testing

SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,1) --2020-02-02 00:00:00.000

SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,2) --2020-02-09 00:00:00.000

SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,3) --2020-02-16 00:00:00.000

SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,4) --2020-02-23 00:00:00.000

SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,32)--2020-02-23 00:00:00.000

IF Utilities.dbo.GetMonthDay('2020-02-11',1,32)='2020-02-23' BEGIN

SELECT 'Run your code'

END

Further reading

I hope this can be of use to you, or it gives you an idea for other processes.

If you need to calculate with holidays then you will need a calendar table, and you can see a great example on the link below.

more: Add working days to date in an SQL Server Function with flexible options

Author

Gavin Clayton
Gavin Clayton
I formed Claytabase in 2010 as a way of carrying on my work with SQL Server and ASP.NET. This has culminated in the Ousia Content Management System being taken from concept to one of the quickest CMS's on the market.

Helpful?

Please note, this commenting system is still in final testing.

Our services

database maintenance | database design | web site design | web site hosting | web site security | cloud services | social media

© 2016 Claytabase Ltd, registered company in the UK 08985867

Quick links

RSS Login ContactCookie PolicySitemap

Find us on social media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in touch

+441983 300 796info@claytabase.co.ukGround Floor, Building 1000, Lakeside North Harbour, Western Road, Portsmouth, Hampshire, United Kingdom, PO6 3EZ

Partners and memberships

Ousia logo
The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Ousia CMS Loader