This post follows my previous two posts about datetime
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-ii.aspx
Now we will see some example queries that invlove date calculation in the WHERE clause
Consider the following data
declare @orders table(order_id int identity(1,1) primary key, order_date datetime, order_value decimal(14,2))
insert into @orders(order_date,order_value)
select '2001-07-16 00:52:26.913', 332.38 union all
select '2001-02-10 11:00:39.003', 111.61 union all
select '1999-01-12 13:03:42.147', 265.83 union all
select '1995-12-15 13:04:42.667', 141.34 union all
select '2000-09-10 16:09:58.817', 251.30 union all
select '1996-10-15 17:52:51.607', 258.17 union all
select '2004-11-22 10:33:05.947', 222.98 union all
select '2005-07-15 03:29:47.653', 3148.33 union all
select '2006-03-16 19:08:30.423', 213.97 union all
select '2006-02-21 15:38:42.770', 381.91 union all
select '2007-12-23 00:28:14.490', 1140.51 union all
select '2007-10-24 10:19:59.220', 33.25 union all
select '2008-07-28 15:14:30.350', 155.09 union all
select '2005-03-30 06:40:25.473', 23.05 union all
select '1995-12-15 03:05:35.230', 348.29 union all
select '2010-01-31 15:02:43.220', 3146.06
Some queries based on the data
1 Find orders placed on December 15,1995
select * from @orders
where order_date>='19951215' and order_date<'19951216'
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
4 1995-12-15 13:04:42.667 141.34
15 1995-12-15 03:05:35.230 348.29
2 Find orders placed from April 1,2001 to July 31, 2001
select * from @orders
where order_date>='20010401' and order_date<'20010801'
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
1 2001-07-16 00:52:26.913 332.38
3 Find orders placed in Year 2006
select * from @orders
where order_date>='20060101' and order_date<'20070101'
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
9 2006-03-16 19:08:30.423 213.97
10 2006-02-21 15:38:42.770 381.91
4 Find total order values for each year
select dateadd(year,datediff(year,0,order_date),0) as date_year,sum(order_value) as order_value from @orders
group by dateadd(year,datediff(year,0,order_date),0)
Result
date_year order_value
----------------------- ---------------------------------------
1995-01-01 00:00:00.000 489.63
1996-01-01 00:00:00.000 258.17
1999-01-01 00:00:00.000 265.83
2000-01-01 00:00:00.000 251.30
2001-01-01 00:00:00.000 443.99
2004-01-01 00:00:00.000 222.98
2005-01-01 00:00:00.000 3171.38
2006-01-01 00:00:00.000 595.88
2007-01-01 00:00:00.000 1173.76
2008-01-01 00:00:00.000 155.09
2010-01-01 00:00:00.000 3146.06
5 Find orders placed on July month
select * from @orders
where order_id>0 and month(order_date)=7
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
1 2001-07-16 00:52:26.913 332.38
8 2005-07-15 03:29:47.653 3148.33
13 2008-07-28 15:14:30.350 155.09
Note that I have used order_id>0 in order to make use of the index
6 Find orders placed on or after 12 PM
select * from @orders
where order_id>0 and datepart(hour,order_date) >=12
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
3 1999-01-12 13:03:42.147 265.83
4 1995-12-15 13:04:42.667 141.34
5 2000-09-10 16:09:58.817 251.30
6 1996-10-15 17:52:51.607 258.17
9 2006-03-16 19:08:30.423 213.97
10 2006-02-21 15:38:42.770 381.91
13 2008-07-28 15:14:30.350 155.09
16 2010-01-31 15:02:43.220 3146.06
7 Find orders placed between 3 PM to 7 PM
select * from @orders
where order_id>0 and
order_date>=dateadd(day,datediff(day,0,order_date),'15:00:00') and
order_date<=dateadd(day,datediff(day,0,order_date),'19:00:00')
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
5 2000-09-10 16:09:58.817 251.30
6 1996-10-15 17:52:51.607 258.17
10 2006-02-21 15:38:42.770 381.91
13 2008-07-28 15:14:30.350 155.09
16 2010-01-31 15:02:43.220 3146.06
8 Find orders placed on 10th day of each month
select * from @orders
where order_id>0 and datepart(day,order_date) =10
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
2 2001-02-10 11:00:39.003 111.61
5 2000-09-10 16:09:58.817 251.30
9 Find orders placed on Second week of each month (week range 1-7 is first week, 8-14 is second week etc)
select * from @orders
where order_id>0 and datepart(day,order_date) between 8 and 14
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
2 2001-02-10 11:00:39.003 111.61
3 1999-01-12 13:03:42.147 265.83
5 2000-09-10 16:09:58.817 251.30
10 Find orders placed on Second saturday of each month
select * from @orders
where
order_id>0 and datepart(day,order_date) between 8 and 14 and
datepart(weekday,order_date)=7
Result
order_id order_date order_value
----------- ----------------------- ---------------------------------------
2 2001-02-10 11:00:39.003 111.61
Here are some answers for the frequently asked questions based on getdate() like removing time part,finding first day of a month,
last day of a month,etc
Run the query and see the result and column names. They are self explanatory
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(day,getdate()) as current_day,
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
Refer Part IV that gives more informations on the best practises when using date values