SQL Question - comparing dates

[TechBoyJK]

Hi Guys,

I'm trying to build a query that will search our user database for users that are within a certain age range.

User's birthday is kept as a smalldatetime

The form will ask for two ages.. a min and max..

For instance 18-45

I am using CFML, and it has a function called datediff that I used to deduct the actual datetime for each age and create a datetime variable...

However, when I try and work the select statement and choose records that fall within the range, I am not getting any matches.

For instance, one member has the birthday of (the time stamp is what is in the DB

birthday - {ts '1980-06-19 00:00:00'}

I've tried both variations of SQL statements below. The query runs, but it doesn't return any matches, even though there should be plenty.

Select *

Where birthday between {ts '1961-08-24 12:43:18'} AND {ts '1995-08-24 12:43:18'}

AND

Where birthday >= {ts '1961-08-24 13:57:16'} AND birthday <= {ts '1995-08-24 13:57:16'}


What am I doing wrong?

 

[Train]

which DB are you using?

On SQL Server, I usually pass datetimes like this:

WHERE birthday BETWEEN '1961-08-24 12:43:18' AND '1995-08-24 12:43:18'

Usually I would use params:

WHERE birthday BETWEEN @BeginDate AND @EndDate

For both security and caching reasons, but that's not always an option.

 

[TechBoyJK]

SQL Server 2008

I'm using smalldatetime format and records are kept in this format

{ts '1961-08-24 12:43:18'}

 

[WannaFly]

I agree with Train - use what he suggested.

I'm not familiar with the {ts} format, but reading this link:
http://www.sqlteam.com/article/timestamps-vs-datetime-data-types

It looks like it might return a timestamp, which it says is useless for comparison which means your query would return no results.