Epic epic sql working today.

This little thing calculates the first day given a year and week number, and the start day of the next week. I have no idea whether it's US week number compliant or ISO...whatever the number is...

but this is what im using anyway;

DECLARE @iYear AS INT = 2010
DECLARE @iWeek AS INT = 1


DECLARE @compareTo AS Datetime = CONVERT(datetime, CONVERT(varchar(4),@iYear))
PRINT 'Date from given year: ' + CONVERT(varchar(100),@compareTo)

print '---------'
print ''

PRINT 'First day of week of specified week: ' + CONVERT(varchar(100),DATEADD

(week, @iWeek - 1, @compareTo))

DECLARE @oldYear AS int = DATEPART(year, DATEADD(week, @iWeek - 1, @compareTo))
DECLARE @oldWeek AS int = DATEPART(week, DATEADD(week, @iWeek - 1, @compareTo))

PRINT 'Year + month of old week: ' + CONVERT(varchar(10),@oldYear) + CONVERT

(varchar(10),@oldWeek)

print '---------'


DECLARE @newYear AS int = DATEPART(year, DATEADD(week, @iWeek, @compareTo))
DECLARE @newWeek AS int = DATEPART(week, DATEADD(week, @iWeek, @compareTo))

IF (@newYear > @oldYear)
BEGIN
PRINT '## a year has been skipped, so specifying date as first week of

next year'

PRINT 'First day of week after specified week: ' + CONVERT(varchar(50),

CONVERT(datetime, Convert(varchar(4), @newYear)))
END
ELSE
BEGIN

PRINT 'First day of week after specified week: ' + CONVERT(varchar

(100),DATEADD(week, @iWeek, @compareTo))

PRINT 'Year + month of new week: ' + CONVERT(varchar(10),@newYear) +

CONVERT(varchar(10),@newWeek)

END