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

So this has taken me like...an hour to figure out at work so I thought I'd share with everyone;


The problem is, I have to select things from a database, and they always have a start date assigned, but not all have an end date. So how do you properly select everything that falls within those ranges?

THE RULES FOR DATE SELECTION
============================

If there is an END DATE

The Date you are selecting from has to be before or equal to the data's StartDate
The Date you are selecting to has to be greater than the data's EndDate


If there is NO END DATE

The Date you are selecting from has to be before or equal to the data's StartDate
The Date you are selecting to has to be greater than the data's StartDate

To be honest this is more a reminder to myself so I won't forget something so trivially simple made me feel retarded. Yay for solving problems!

another programming tip:

in C#, XmlAttribute.Value will automatically convert entities, but .InnerXml will preserve them.

so if you have an attribute "blah <", attribute.value = "blah <" but attribute.innerxml = "blah <"

woot