Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

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'.

TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations

TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations

Mar 1 2010 7:47AM by 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.

Tags:DATE, DATETIME

Divya Agrawal Shah
89 · 2% · 629
TSQL Beginner's Challenge 2 - Fight Your Fear for Date Calculations , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]