Solved

T-SQL Select Date Range

Posted on 2014-03-17
10
334 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
  • 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:ScottPletcher
ScottPletcher 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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:ScottPletcher
ScottPletcher 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

912 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now