?
Solved

T-SQL Select Date Range

Posted on 2014-03-17
10
Medium Priority
?
343 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 288 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 856 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 572 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 284 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 856 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 572 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 856 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

DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

764 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