Filtering by date

[4dam]

Hi,

I am trying to write a script that will filter a customer database by their date of birth. I would like to be able to display customers who are celebrating their birthdays in the next 30 days.

So in pseudo code it would be:

SELECT * FROM tbl_customer WHERE (birthdays are in the next 30 days)

The date of birth is stored in an access database field (customer_dob), type date/time, in the format DD/MM/YYYY.

Would anyone please be able to provide me with the actual code needed to make this script work?

Many thanks,

Adam

[silverbullet24]

sql = "SELECT * FROM tbl_customer WHERE customer_dob BETWEEN #" & date() & "# AND #" dateadd(d,30,date()) & "#"

[4dam]

Thanks for that, I am not able to test this at the moment, as I am not at my development computer, but looking at the code, is this not saying, for example:

BETWEEN #21/11/2005# AND #21/12/2005#

Obviously, it is their birthday no matter what the year is, so in my understanding this would only return people born in the current year?

[silverbullet24]

ahh yeah i suppose you're right. sorry, wrote that real quick. i was just thinking you could use the month/day functions or the dateadd functions, but that won't really work either (i don't think)

i'll give it some though and maybe test it out before i post anymore crazy stuff

[Giskard]

I'm not very versed on access, but this is the where clause that I use to find birthdates in the next month (not exactly 30 days). I'm sure it can be converted to access.


WHERE (MONTH(BirthDate) = MONTH(GETDATE())) AND (DAY(BirthDate) >= DAY(GETDATE())) OR
(DAY(BirthDate) < DAY(GETDATE())) AND (MONTH(BirthDate) - 1 = MONTH(GETDATE()))

[4dam]

Thanks,

I'm fairly new to ASP, so please forgive me for not really understanding this:

WHERE (MONTH(BirthDate) = MONTH(GETDATE())) AND (DAY(BirthDate) >= DAY(GETDATE())) OR
(DAY(BirthDate) < DAY(GETDATE())) AND (MONTH(BirthDate) - 1 = MONTH(GETDATE()))

Given that my date of birth field is called "customer_dob", would your expressions (Month(Birthdate)) and (Day(Birthdate)) be (Month(customer_dob)) and (Day(Customer_dob)) for me?

And what are (DAY(GETDATE))and (MONTH(GETDATE))?

Again, apologies for my lack of understanding.

[Giskard]

Yes, where I have BirthDate you would substitute Customer_dob.

Unfortunately I messed up the cut and paste and missed the last part of the where clause. For you it should read:


WHERE (MONTH(Customer_dob) = MONTH(DATE())) AND (DAY(Customer_dob) >= DAY(DATE())) OR
(DAY(Customer_dob) < DAY(DATE())) AND (MONTH(Customer_dob) - 1 = MONTH(DATE()))
OR
(DAY(Customer_dob) < DAY(DATE())) AND (MONTH(Customer_dob) - 1 = 0) AND (MONTH(DATE()) = 12))


The following are SQL functions that are used in the query:

DAY() - returns the day number for the date supplied (i.e. a number from 1-31)
MONTH() - returns the month number for the date supplied (i.e. a number from 1-12)
GETDATE() (SQL Server) - DATE() (Access) - is a function that returns the current day.

(NOTE: These are SQL Functions not ASP functions and they run on the database. If you change from an Access database to SQLServer then you will have to verify that the functions are correct for the new database - GETDATE doesn't work for Access, DATE doesn't work for SQLServer)

Basically what this where clause is saying is:

WHERE
the month of the birthdate is equal to the current month and the day of the birthdate is greater than or equal to the current day
OR
the month of the birthdate minus 1 equals the current month and the day of the birthdate is less than the current day
OR
the month of the birthdate minus 1 equals 0 and the current month equals 12 and the day of the birthdate is less than the current day (this is to handle the month of December)

[4dam]

Awesome!

I think I understand this. I will give it a go later

Thanks

[4dam]

Braw!

That worked an absolute treat!

I am eternally grateful, cheers.

Adam