SQL Birthday problem ;)

B

brett

Hi there

Since Standard MS Access
dosnt support dayofyear() function I am haveing problems converting
this sql into access compatable SQL

select clientID, FirstName, SurName, DOB
from CLIENTS
where DAYOFYEAR(DOB) >= DAYOFYEAR('2005-09-01')
and DAYOFYEAR(DOB) <= DAYOFYEAR('2005-10-01')

does anyone know of any equivilent functions to get the day of the
year as a number in MS Access SQL?

Kind Regards

Brett
 
D

Douglas J. Steele

I'm not familiar with a DAYOFYEAR function in SQL, and it's not in my copy
of BOL. What's it supposed to do?
 
R

Ricky Hicks MVP

If you only need to "day of the year" value .. use the DatePart() function
with "y" as the Interval argument to reurn this value ...

R. Hicks
 
B

brett

I'm not familiar with a DAYOFYEAR function in SQL, and it's not in my copy
of BOL. What's it supposed to do?
Click to expand...

Hi Doug, thanks for replying

It gets a Julian date, i.e. it gets the number of the day of the year
for the date specified. E.G.

if I have the date 02/05/2005 dayofyear(02/05/2005) will return
the number 36 (that is 36 days from 01/01/xxxx)

it is great for comparing birthdays.

Basically all I want to do is query does a persons birthday fall
within a range of dates. (this isn't the same as asking is their DOB
within a range of dates) it is so I can mail shot people who's
birthday is coming up soon.

I have it working on php using the previous query but I cannot get a
smaller version of it working in ms access as access doesn't support
the full SQL command set, so I believe.

Thanks for helping out :)

Brett
 
D

Douglas J. Steele

Then, as Ricky suggested, you can use DatePart:

select clientID, FirstName, SurName, DOB
from CLIENTS
where DatePart("y", DOB) >= DatePart("y", #2005-09-01#)
and DatePart("y", DOB) <= DatePart("y", #2005-10-01#)
 
T

tina

i agree with Ricky and Doug, but you might want to use DateSerial() for your
birthday date, rather than the DOB date. otherwise you may run into the
occasional problem due to leap year; presumably you want every single record
to be handled accurately every time.

where DatePart("y", DateSerial(2005, Month(DOB), Day(DOB))) >= DatePart("y",
#2005-09-01#)
and DatePart("y", DateSerial(2005, Month(DOB), Day(DOB))) <=
DatePart("y", #2005-10-01#)

though the following may work just as well:

where DatePart("y", DateSerial(2005, Month(DOB), Day(DOB))) Between
DatePart("y", #2005-09-01#) And DatePart("y", #2005-10-01#)

or even

where DateSerial(2005, Month(DOB), Day(DOB)) Between #2005-09-01# And
#2005-10-01#

to make the expression portable from year to year, try

where DateSerial(Year(Date()), Month(DOB), Day(DOB)) Between
DateSerial(Year(Date()), 9, 1) And DateSerial(Year(Date()), 10, 1)

or

where DatePart("y", DateSerial(Year(Date()), Month(DOB), Day(DOB))) Between
DatePart("y", "9/1") and DatePart("y", "10/1")

hth
 
G

Guest

Why not try something like

select clientID, FirstName, SurName, DOB
from CLIENTS
where format(DOB,"mm-dd") >= "09-01"
and format(DOB,"mm-dd") <= "10-01"

The only time it wouldn't work is when you try to cross the year boundary.

Brian
 
Top