[thelist] SQL query help: dates

[Joshua Olson]

> -----Original Message-----
> From: Madhu Menon
> Sent: Thursday, February 17, 2005 2:26 PM
> 
> Anthony Baratta wrote:
> 
> >Here's a little pseudo code...
> >
> >dtNow = DateValue(Now()) & " 00:00:00"
> >dtThen = DateValue(Now() + 10) & " 23:59:59"
> >
> >select CustomerName from tblCustomers
> >where birthday between "dtNow" and "dtThen"
> 
> Won't work. One thing to remember is that birthdays also 
> record the year of 
> birth. I'm 30 Nov, 1975, so my birth date would not fall in the range 
> you've specified (between now and 10 days from now.) ;)
> 
> I need a way to just compare months and days and leave the 
> year of birth 
> out of it.
> 
> (And that's also why DateDiff doesn't help.)


Madhu,

I don't prefer this approach because it would be problematic around the
start of the new year.

I think the appropriate course of action would be to use the date functions
provided by the database (datediff, for example) to calculate the person's
next birthday.  

Something like this (syntax untested, but hopefully it's clear):

DateAdd("yyyy", DateDiff("yyyy", birthday, Now()) + 1, Now())

Then, drop that into the DateDiff to figure out the number of days from
Now() until that value.

Less than or equal to 10?

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168