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