All possible SQL Date Operations

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

Next Recommended Reading CRUD Operation in SQL Server