Solved

T-SQL Select Date Range

Posted on 2014-03-17
10
338 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: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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot with row total 5 30
Query for timesheet application 3 20
SQLCMD Output to file from xpcmd_shell 6 20
SQL 2008 R2 problem with DB 9 16
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

820 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