Select date from two diferent years

In sql select statement, can I do like the following? I am interested in values in between  '01/01/2014 and orderdate <= '01/31/2014'  and
orderdate >= '01/01/2015 and orderdate <= '01/31/2015'

select id, orderdate, itemshipped where
(orderdate >= '01/01/2014 and orderdate <= '01/31/2014' )or (orderdate >= '01/01/2015 and orderdate <= '01/31/2015')
VBdotnet2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SThayaTechnical MAnagerCommented:
Hi,

 As per your  sql query missing the from clause..


select id, orderdate, itemshipped  
from <Table Name>
where
(orderdate >= '01/01/2014' and orderdate <= '01/31/2014' )or (orderdate >= '01/01/2015' and orderdate <= '01/31/2015')
0
SThayaTechnical MAnagerCommented:
You can use between operator also,please refer the below query
create table [#temp]
(
ID int,
orderdate datetime,
itemshipped  int
)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/01/2014',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/03/2014',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/04/2014',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/30/2014',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/31/2014',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'02/02/2014',10)


insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/01/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/03/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/04/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/30/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'01/31/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'02/02/2015',10)
insert into [#temp] (ID,orderdate,itemshipped)  VALUES (1,'02/06/2015',10)

select * from [#temp]

select id, orderdate, itemshipped  
from [#temp]
where
(orderdate >= '01/01/2014' and orderdate <= '01/31/2014' )or (orderdate >= '01/01/2015' and orderdate <= '01/31/2015')


select id, orderdate, itemshipped  
from [#temp]
where
(orderdate  BETWEEN '01/01/2014' AND   '01/31/2014')
OR
(orderdate  BETWEEN '01/01/2015' AND   '01/31/2015')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
don't use "between" for date ranges*

also the safest possible date literal format for SQL Serer is YYYYMMDD

so, a much safer syntax approach is:

select id, orderdate, itemshipped
from your_table
where (orderdate >= '20140101' and orderdate < '20150101' )
or (orderdate >= '20150101' and orderdate < '20160101')

but note that this could be simplified to:

select id, orderdate, itemshipped
from your_table
where (orderdate >= '20140101' and orderdate < '20160101' )

Note that this syntax uses LESS THAN 01/01/year+1
not: "less than or equal" 12/31/year

---
* please see: "Beware of Between"
also:
the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.
http://sqlmag.com/t-sql/t-sql-best-practices-part-2
Itzik Ben-Gan
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The above answers are correct.  You can also use the MONTH and YEAR functions.
select id, orderdate, itemshipped
FROM your_table
WHERE MONTH(orderDate) = 1 AND YEAR(orderDate) IN (2014, 2015) 

Open in new window

0
PortletPaulfreelancerCommented:
actually Jim I now see I was not correct, I had missed the fact only January was wanted from 2 years*, but it can be corrected.

select id, orderdate, itemshipped
from your_table
where (orderdate >= '20140101' and orderdate < '20140201' )
or (orderdate >= '20150101' and orderdate < '20150201')

Note here that instead of needing to know the last day of a month, we always use day 1, but for the later date (1st of Feb) we have moved to the next month.

again, I implore you not to use between for date ranges. and also note that by avoiding the use of functions on the data the filtering conditions are "sargable" (can use indexes)

----
* part of this is due to the fact that I never use MM/DD/YYYY in my daily life as my culture uses dd/mm/yyyyy.
Placing the month first is just so weird (to me).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.