TSQL date teaser. Bonus Question

Given a date, how do you tell which week in the month is that date?
EG : Given 1-Feb-2005, it is the 1st week in February.
Given 17-Feb-2005, it is the 3rd week in February.

declare @startOfMonth datetime
declare @myDate datetime
declare @weekOfYearForStartOfMonth int
declare @weekInMonth int

select @myDate = '17-feb-2005'
select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
select @weekOfYearForStartOfMonth = datepart(ww, @startOfMonth)
select datepart(ww, @myDate) - @weekOfYearForStartOfMonth + 1

C# Language Innovation. Must Watch!

Anders Hejlsberg has a great video on Channel 9 which show case the great innovation they have done on C# 3.0 and .NET Framework 3.0.

The Language Integrated Query (LINQ) and type inferencing capability is really impressive.

Anders Hejlsberg - LINQ

TSQL date teaser. Answer for Q6

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.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToAdd int
declare @NthWeek int
declare @TargetDayOfWeek int

select @myDate = '10-July-2005'

select @TargetDayOfWeek = 5
select @NthWeek = 2

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)

select @DaysToAdd = (7 - ((datepart(dw,@startOfMonth) +
                        (7 - @TargetDayOfWeek)) % 7)) % 7

select dateadd(d, @DaysToAdd + ((@nthWeek-1)*7), @startOfMonth)

TSQL date teaser. Answer for Q5

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.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToAdd int
declare @TargetDayOfWeek int

select @myDate = '10-jul-2005'
select @TargetDayOfWeek = 3

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
print  @startOfMonth

select @DaysToAdd = (7 - ((datepart(dw,@startOfMonth) +
                          (7 - @TargetDayOfWeek)) % 7)) % 7

select dateadd(d, @DaysToAdd, @startOfMonth)

TSQL date teaser. Answer for Q4

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.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToMinus int
declare @NthWeek int
declare @TargetDayOfWeek int

select @myDate = '10-jul-2005'
select @NthWeek = 2
select @TargetDayOfWeek = 5

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
select @startOfNextMonth = dateadd(m, 1, @startOfMonth)
select @endOfMonth = dateadd(d, -1 , @startOfNextMonth)

select @DaysToMinus = ((datepart(dw,@endOfMonth) + (7-@TargetDayOfWeek)) % 7)
select dateadd(d, -(@DaysToMinus + ((@nthWeek-1)*7)), @endOfMonth)

TSQL date teaser. Answer for Q3

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.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToMinus int

select @myDate = '10-jul-2005'

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
print  @startOfMonth

select @startOfNextMonth = dateadd(m, 1, @startOfMonth)
print  @startOfNextMonth

select @endOfMonth = dateadd(d, -1 , @startOfNextMonth)
print  @endOfMonth

select @DaysToMinus = -((datepart(dw,@endOfMonth) + 1) % 7)

select dateadd(d, @DaysToMinus, @endOfMonth)

TSQL date teaser. Answer for Q2

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.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)

select @myDate = '21-feb-2005'

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)
print  @startOfMonth

select @startOfNextMonth = dateadd(m, 1, @startOfMonth)
print  @startOfNextMonth

select @endOfMonth = dateadd(d, -1 , @startOfNextMonth)
print  @endOfMonth 

TSQL date teaser. Answer for Q1

This is a long outstanding post to my previous TSQL date teaser post.

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.

declare @dayOfWeek int
declare @startOfWeek datetime
declare @endOfWeek datetime
declare @myDate varchar(20)

select @myDate = '28-july-2005'
select @dayOfWeek = datepart(dw, @myDate)

select @startOfWeek = dateadd(d, (@dayOfWeek - 1)* -1, @myDate)
print  @startOfWeek

select @endOfWeek = dateadd(d, 7 - @dayOfWeek , @myDate)
print  @endOfWeek

Free XML Editor

I always keep some freeware tools in my thumbdrive and take it with me wherever I go. It is especially useful when I do work at customer place. I obviously can't install unlicense software in their machine, so the option will be to use free tools.

Here are 2 FREE! XML editor which I find very nice.

XMLFox freeware XML Editor
XML and XSD editing

XMLPad
XML Editing.
XSLT Transform.
XPath Evaluation.