Solved

T-SQL Select Date Range

Posted on 2014-03-17
10
341 Views
Last Modified: 2014-03-17
In T-SQL (SQL Server 2000), I am trying to bring back only orders that where purchased during a specified month.  
The field that contains the order date is Item_Order_Date and the date is specified as:
yyyy-mm-dd
For example if I wanted to select the orders that were done in March, but going back to prior years as well.
I only want to filter on the the month value.   Any ideas?  Thanks
0
Comment
Question by:fjkaykr11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 72 total points
ID: 39934549
SELECT blah, blah, blah
FROM your_table
WHERE MONTH(Item_Order_Date) = 3
0
 
LVL 4

Assisted Solution

by:Kent Fichtner
Kent Fichtner earned 214 total points
ID: 39934550
I would use the MONTH function.  If you wanted all order that were done in march then you would use:

MONTH(ITEM_ORDER_DATE) = 3

They also have day and year.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 143 total points
ID: 39934560
Hopefully "Item_Order_Date" is a datetime column.  If it is, code as below.

WHERE
    ((Item_Order_Date >= '20140301' AND Item_Order_Date < '20140401') OR
     (Item_Order_Date >= '20130301' AND Item_Order_Date < '20130401') OR
     (Item_Order_Date >= '20120301' AND Item_Order_Date < '20120401')
     --...other year(s), if needed
    )


If and only if "Item_Order_Date" is a varchar (yikes!), then add the dashes to the literal date strings.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 71 total points
ID: 39934561
If your date field is a text field then you might need to do some string manipulation to get the value out.

Is ITEM_ORDER_DATE a datetime field or a varchar field?
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39934581
Thanks for all the replies the data type for the ITEM_ORDER_Date field
indicates datetime.  Please let me know what is the best method for this,  thanks.
0
 
LVL 4

Assisted Solution

by:Kent Fichtner
Kent Fichtner earned 214 total points
ID: 39934609
The first two are the best and simplest options.  It would pick out all rows that hit in the 3rd month regardless of year or day.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 143 total points
ID: 39934645
The first two will be forced to do full table scans, no matter what indexes are present.

The date range checks can use any indexes available.

In general, never use a function on a table column unless it's absolutely unavoidable.
0
 
LVL 4

Assisted Solution

by:Kent Fichtner
Kent Fichtner earned 214 total points
ID: 39934661
I agree with Scott.  I use SQL for a lot of reports (crystal reports) so i have learned to never static a data in your query such as Scott's example.  If you are running it from SQL and you remember to add new years, his query is the way to go.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39934667
@ScottPletcher thanks for the updated info. The date range goes back years and years and years so having to write the query specifying each year is going to be cumbersome.  This isn't a production database and I am not concerned how long the query takes to run.
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 39934738
Thanks for the help
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question