### TSQL Date Teaser

Do you think you can solve the following problems using SQL Server TSQL ?

1) Given a date, find the date of the first day and last day of that week.

E.G: Given 28-July-2005, You have to tell 24-July-2005 is the first day

of that week and 30-July-2005 is that last day of that week.

2) Given a date, find the date of the first day and last day of the month.

E.G: Given 21-July-2005, You have to tell 1-July-2005 is the first day of

the month and 31-July-2005 is the last day of the month.

3). Given a date, find the last Friday of the month.

E.G: Given 10-July-2005, last Friday for the month is 29-July-2005.

4) A slight variation from (3), given a date, find the second last Thursday

of the month.

E.G: Given 10-July-2005, second last Thursday of the month is 21-July-2005.

Your solution must also be able to find (N)th last (day of the week) of the month.

Where : N can be last week, second last week, third last week and so on,

day of week can be any day from Sunday to Saturday.

5). Given a date, find the first Tuesday of the month.

E.G: Given 10-July-2005, first Tuesday for the month is 5-July-2005.

6) A slight variation from (5), given a date, find the second Thursday

of the month.

E.G: Given 10-July-2005, second Thursday of the month is 14-July-2005.

Your solution must also be able to find (N)th (day of the week) of the month.

Where : N can be first week, second week and so on.

day of week can be any day from Sunday to Saturday.

Condition :

1) You can only use TSQL Date and Time function.

2) Only simple expressions are allowed. (EG : add, minus, multiply, divide, assignment, variables).

3) Language element such as Loop, if..else, case statement are not allowed.

4) Assume first day of the week is Sunday and last day of the week is Saturday(which is default in most

SQL Server installation).

5) You can't deal with leap year explicitly. Meaning, you cannot write your own algorithm to check

for leap years. You are only allowed to use TSQL DateTime function.

Try it and I will post the solutions by the next 2 weeks.

Labels: sql