T-SQL Beginners’ Challenge #2 – Fight Your Fear for Date Calculations

This puzzle is part of the TSQL Challenge contest.

Author: Divya Agrawal Shah

Introduction

This challenge invites you to play with date calculations. A lot of people are scared of date calculations and this challenge aims to help them to fight their fear!

This challenge is to find the date based on year, month, day of the week and weekday number. Say for example, if the question is to find the date of 2nd Sunday of January 2010, the answer should be ‘2010-01-10’.

Sample Data

Yr    Mon  Dy    Dyno
----------------------
2010  Jan  Sun    2
2005  Jan  Mon    3
1995  Feb  Sun    1
2000  Feb  Wed    4
1982  Mar  Tue    2
2010  Mar  Tue    8

Expected Results

Yr     Mon    Dy   Dyno  Date
----------------------------------------
1982    Mar    Tue    2   1982-03-09
1995    Feb    Sun    1   1995-02-05
2000    Feb    Wed    4   2000-02-23
2005    Jan    Mon    3   2005-01-17
2010    Jan    Sun    2   2010-01-10
2010    Mar    Tue    8   Invalid Date

Rules

  1. If the Date does not exist show it as ‘Invalid Date’.
  2. The solution should be a single query that starts with “SELECT” or “;WITH”
  3. The output should be sorted on the Date result.

Sample Script

DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)
INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 UNION ALL
SELECT 2005,'Jan','Mon',3 UNION ALL
SELECT 1995,'Feb','Sun',1 UNION ALL
SELECT 2000,'Feb','Wed',4 UNION ALL
SELECT 1982,'Mar','Tue',2 UNION ALL
SELECT 2010,'Mar','Tue',8
 
SELECT * FROM @tbl

Restrictions

Notes

  1. The goal is to find the correct date based on the Week Day number regardless of the starting week day of the month. Say, for example January, 2010 starts from Friday and hence Sunday on ‘2010-01-03’ cannot be considered as the 2nd Sunday, although it is in the second week. It will be considered as the 1st Sunday.
  2. The solution should work on SQL Server 2005 and above.