Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query syntax for dates

Posted on 2014-08-27
12
Medium Priority
?
249 Views
Last Modified: 2014-08-28
Using MS SQL 2008

Table "users'  has FirstNm, LastNm and DOB.

I need to query a that will return all users who will be X yrs old within  date "A" and date "B". This will be parameters passed on from an ASP page.

Say X = 21
A = today
B = Dec 31st.

What would the query syntax be ?
0
Comment
Question by:Aleks
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 572 total points
ID: 40288552
Roughly:

DECLARE @start_date datetime
DECLARE @end_date datetime

SET @start_date = GETDATE()
SET @end_date = '20141231'

SELECT *
FROM tablename
WHERE
    DOB >= DATEADD(YEAR, -21, @start_date) AND
    DOB < DATEADD(DAY, 1, DATEADD(YEAR, -21, @end_date)) --I always use < rather than <=
        --when dealing with date/datetime, so I add an extra day
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 288 total points
ID: 40288878
Conversely -
select * from table
where dateadd(year,21,DOB) between getdate() and '20150101'
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 572 total points
ID: 40288931
1) don't use a function on a table column unless absolutely forced to
2) if you use between, the end date should be '20141231', not '20150101'.
0
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!

 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 856 total points
ID: 40289228
Try: table a(...., dob)
declare @X int  
declare @A date 
declare @B varchar(10)

SET @X=21;
SET @A=GETDATE();
SET @B='DEC 31';

SELECT * FROM a 
WHERE dateadd(year,@X,dob) Between @A AND convert(date, @B + ' ' + convert ( varchar(4),DATEPART(YEAR,@A)));

Open in new window

You may skip @A by directly using  GETDATE().
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 284 total points
ID: 40289396
Don't run functions on the data to suit a single filter criteria:
Doing that means you are running one or more calculation for every row of data (hundreds, thousands .... maybe millions) of calculations that you don't need AND you remove the ability to use an index as well.

The best way is as shown by Scott above

WHERE
    DOB >= DATEADD(YEAR, -21, @start_date) AND
    DOB < DATEADD(DAY, 1, DATEADD(YEAR, -21, @end_date)) --I always use < rather than <= 
        --when dealing with date/datetime, so I add an extra day

Open in new window

Notice that the field DOB is NOT altered in any way

Finally: between sucks for date ranges, don't use it.

Itzik Ben-Gan
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.
& also: Beware of Between
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40289501
Alert!
In Access 2013 this works:

SELECT * FROM tbl
WHERE fld BETWEEN 10 AND 1

Open in new window


And translation into >= AND <= does not work unless finding out which value is smaller.

So one should understand the keywords for each environment.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40289518
@hnasr

The question starts with: "Using MS SQL 2008" so I'm not sure why Access is relevant.
It is interesting that Access allows reversal of values, but the sequence of values isn't the issue I'm raising.

If you read my article you will see I refer to the definition of BETWEEN across several products, they differ in words, but the meaning is consistent, and the mathematical representation is always:

expression >= low-value and expression <= high-value

In most RDBMSs between is sensitive to the sequence, if you get this wrong you get no results.
So, I do understand the keyword very well.

But in addition:
FOR DATE RANGES
You will notice that in fact I (and many others) propose NOT using <=
Instead the upper boundary is determined by <
With the value being "the next unit", usually the next day
This is the critical difference.

---------
btw: Use of between for integers can be quite fine, I'm only discussing date ranges.

We could take this off-line if you would like via messages?
0
 

Author Comment

by:Aleks
ID: 40289550
Lots of replies :) .. I will try the MS SQL ones tomorrow
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 856 total points
ID: 40289559
"btw: Use of between for integers can be quite fine, I'm only discussing date ranges."

But with Access the same applies with Dates.

SELECT *
FROM a
WHERE (((dt) Between #1/1/2016# And #1/1/2012#));

Open in new window

End of discussion, from my part, to avoid off course argument.
0
 

Author Comment

by:Aleks
ID: 40289561
But that doesn't really answer the question, that will give me results in which DOB falls between those dates, not those who turn 21 between those dates
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40289566
and for equal opportunity, also my last here (+edit: about between)

It does not matter to me, at all, if you can do high-value first (in Access)

it does not alter the fact that, incorrectly, #1/1/2016# at midnight is included in the results if you use between. To me the correct non-overlapping period of years, 2012, 2013, 2014 and 2015 is:

SELECT *
FROM a
WHERE ( (dt) < #1/1/2016# And (dt) >= #1/1/2012# );

or (more conventionally, low then high)

SELECT *
FROM a
WHERE ( (dt) >= #1/1/2012# And (dt) < #1/1/2016# );
0
 
LVL 31

Accepted Solution

by:
hnasr earned 856 total points
ID: 40289619
Try: Assuming date1 and date2 are greater than dob.

SELECT * FROM tbl
WHERE 21 Between datediff(year, dob, date2) AND datediff(year, dob, date1)
0

Featured Post

Industry Leaders: 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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

810 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