Selecting by Date in SQL Server

Yes, I'm still around. I hadn't been working on anything new for a while, hence no posts as I figured things out.

Bloggertizer (keyword link: get paid to blog) is growing nicely; up to over 250 blogs listed now. If you own a blog, check it out.

As I mentioned in my Muay Thai Developer post, I take Muay Thai lessons. It didn't take me too long to discover how fractured the Tampa Bay Martial arts community is. Being a problem solver by nature, I decide to try to bring the community together and TampaMartialArts.org (keyword link: Tampa Martial Arts) was born.

Rather than build the site from scratch, I went WebWizForum, a prepackaged forum written in ASP. I chose their hosted solution for $8.00 a month, which includes a brand-free version of the forum, all set up and ready to go.

Out of the box, the site defaulted to default.asp. I wanted more of a home page to greet new visitors, so I went into the control panel and changed the default page to index.asp and wrote a custom home page that draws from forums posts.

One of the presentations I make on the home page is a list of upcoming events. I noticed the list was showing past events, event though I was using a WHERE clause that should have been filtering out past events (pseudo code):

WHERE TheDate >= Date()

Now why didn't that work?

One thing I had noticed was that the server the site was hosting on had the date default set to UK format. In the US we write December 6, 2007 as 12/6/07, in the UK it's 06/12/07. Yet my Date() was returning the right value formated to match the default set on the server.

So I ended up using a different method to sort by date. There's a built in style parameter in SQL server you can use with the convert function that converts a date to number.

Convert(varchar,TheDate,112)

This ends up with a YYYYMMDD integer (20071206 for example). Now I needed to compare it to today's date:

Year()&Month()&Day()

But guess what? If the month or day is less than 10, this returns only 1 digit; the SQL conversion returns 2, with a 0 in front if the number is less than 10. So I needed to add a 0 in front of the number if the month or day was less than 10. Easy:

Year()&Right("0"&Month(),2)&Right("0"&Day(),2)

What this does is put a leading 0 in front of the number, then grab the right two characters. So for a number less than 10 (6 for example) , I end up with 06, grab right two and I get 06. If the date is greater than 9, I end up with three characters, but I only grab the right two: 011 ends up as 11. So I ended up with:

WHERE convert(varchar,TheDate,112) <= "&Year()&Right("0"&Month(),2)&Right("0"&Day(),2)&" ORDER BY TheDate ASC

The result is a record set containing records where TheDate is greater than or equal to the current date.