When I was at my previous project we were using SQL Server 2005 (now we use Oracle 11g) and we used to do a lot of date validations and a lot of date calculations. The following query became our key reference and it used to help us a lot:

select

getdate() as today_with_time,

dateadd(day,datediff(day,0,getdate()),0) as today_without_time,

dateadd(day,datediff(day,0,getdate())-1,0) as yesterday,

dateadd(day,datediff(day,0,getdate())+1,0) as tomorrow,

dateadd(month,datediff(month,0,getdate()),0) as first_day_of_month,

dateadd(month,datediff(month,0,getdate())+1,-1) as last_day_of_month,

dateadd(year,datediff(year,0,getdate()),0) as first_day_of_year,

dateadd(year,datediff(year,0,getdate())+1,-1) as last_day_of_year,

dateadd(week,datediff(week,0,getdate()),0) as first_day_of_week,

dateadd(week,datediff(week,0,getdate())+1,-1) as last_day_of_week,

dateadd(quarter,datediff(quarter,0,getdate()),0) as first_day_of_quarter,

dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as last_day_of_quarter,

dateadd(hour,datediff(hour,0,getdate()),0) as starting_time_of_current_hour,

dateadd(hour,datediff(hour,0,getdate())+1,-1) as ending_time_of_current_hour,

dateadd(minute,datediff(minute,0,getdate()),0) as starting_time_of_current_minute,

dateadd(minute,datediff(minute,0,getdate())+1,-1) as starting_time_of_current_minute,

datepart(hour,getdate()) as current_hour,

datepart(minute,getdate()) as current_minute,

datepart(second,getdate()) as current_second,

datepart(month,getdate()) as current_month,

datepart(year,getdate()) as current_year,

datepart(week,getdate()) as current_week,

datepart(quarter,getdate()) as current_quarter,

datepart(dayofyear,getdate()) as day_of_year,

datename(weekday,getdate()) as current_weekday,

datename(month,getdate()) as current_month_name

HTH

Happy Queyring!!!

Regards,

Raja