So here we go with some useful DATETIME functions.

Dates and times are to be found at the very core of SQL programming, especiually when, like me, you're dealing with SQL Reports.

To convert a date time into zero hours and mins on that date:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
The example above uses GETDATE() but you could replace that with any DATETIME field to make the conversion scaleable.

You may want to set a report date start and end parameters to automatically be the first and last day of the previous week, or month, or the current month to date. These two cheeky little monkeys will give you the first and last days of the previous month:
SELECT dDATEADD(mm,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as FirstDayOfPreviousmonth
SELECT DATEADD(s,-1,DATEADD(month,DATEDIFF(month,0,GETDATE()),0)) as LastDayOfPreviousmonth

To get the dates for current month so far, use this as your start date:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) as FirstDayOfCurrentmonth
and GETDATE() as your end date

What about the last full week (assuming here that you work from Monday to Sunday)?
SELECT DATEADD(ss,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as MondayMorningOfLastWeek
SELECT DATEADD(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) as LateLastSundayEvening

To find the start of the current financial year (In the UK, the financial year runs from 1st April):
SELECT DATEADD(month,-CASE WHEN DATEPART(month,GETDATE())>3 THEN DATEPART(month,GETDATE())-4 ELSE DATEPART(month,GETDATE()) + 8 END,DATEADD(month, DATEDIFF(month,0, GETDATE()),0)) as FiscalYearStart (This probably doesn't need to be quite so complicated as this, why don't you have a go at simplifying it?)

There are all sorts of cunning tricks you can do with DATETIMEs and some can be a bit confusing, at least until someone explains it so that's what we'll do here. Let's break down the first function I showed you:
SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)

We'll work through it in tutorial style, so type SELECT GETDATE() into your SQL Server 2005 Management Studio query pane.

When I do this it gives me: 2010-06-01 15:26:37.937 which is the current date and time to the nearest milisecond. Now wrap that GETDATE function in a DATEDIFF so it looks like this: SELECT DATEDIFF(DAY,0,GETDATE()) and when you press execute it should output something like this: 40328

This number represents the DATE DIFFERENCE in DAYS between day zero and today. Day zero is January 1st 1900. Now we wrap that code in a DATEADD like so: SELECT DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE())) which outputs 2010-06-01 00:00:00.000. What we've done there is to say take day zero, and add 40328 days onto it and because day zero is set to midnight, then when we add forty odd thousand days onto it, it still comes out as midnight.

So to put all that in laymans terms, we're saying How many days is it since 1st January 1900? Let's add that number of days ONTO 1st Jan 1900 to get the date and time of midnight today (or last night).

You can break down all the code above in similar terms and you'll soon have an understanding of how it all works.

Happy squirelling.