Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Understanding Datetime column - Part III

Jun 21 2010 6:50AM by Madhivanan   


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 

Tags: t-sql, sql_server, datetime, BRH, datatype, #TSQL,


Madhivanan
3 · 40% · 12862
3
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Understanding Datetime column - Part III" rated 5 out of 5 by 3 readers
Understanding Datetime column - Part III , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]