Solved

T-SQL Select Date Range

Posted on 2014-03-17
10
339 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 65

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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