• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Query syntax for dates

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
Aleks
Asked:
Aleks
  • 4
  • 3
  • 2
  • +2
7 Solutions
 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
Conversely -
select * from table
where dateadd(year,21,DOB) between getdate() and '20150101'
0
 
Scott PletcherSenior DBACommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
hnasrCommented:
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
 
PortletPaulfreelancerCommented:
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
 
hnasrCommented:
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
 
PortletPaulfreelancerCommented:
@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
 
AleksAuthor Commented:
Lots of replies :) .. I will try the MS SQL ones tomorrow
0
 
hnasrCommented:
"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
 
AleksAuthor Commented:
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
 
PortletPaulfreelancerCommented:
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
 
hnasrCommented:
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
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now