Thread: BirthDay SQL Issue
Hi there This is my first posting here, please forgive me if I make any mistakes here. Ok I have the structure {CLIENTS} = Client ID, First Name, Surname, DOB, Address, Home Phone No, Mobile Phone No As one relation / table (There are several others in this db that are not related to this issue) My problem is this I need to be able to select two dates on my webby so I have a range of dates E.G. (using English date format DD/MM/YYYY) 01/09/2005 to 01/10/2005 Then I want to be able to get, using some nifty query, everyone's birthday that falls between those two dates. So if someone's birthday was 04/09/79 it would return that tuple I have been stuck on this for ages. Any my search for help has brought me here. If anyone out there can help me, in any way, I would be very grateful. Kind regards Brett Halligan
brett wrote: > I need to be able to select two dates on my webby so I have a range of > dates > Then I want to be able to get, using some nifty query, everyone's > birthday that falls between those two dates. So if someone's birthday > was 04/09/79 it would return that tuple > > I have been stuck on this for ages. Any my search for help has brought > me here. You need to look at it from a different angle - don't try comparing dates, compare text instead. => SELECT to_char('2005-03-04'::date, 'MMDD'); to_char --------- 0304 (1 row) That should do it. Oh - you might want to read up on functional indexes in the manual too, if this is a query you'll be running regularly. -- Richard Huxton Archonet Ltd
brett <bhalligan@yahoo.com> schrieb: > Hi there > > This is my first posting here, please forgive me if I make any > mistakes here. > > Ok > > I have the structure > > {CLIENTS} = Client ID, First Name, Surname, DOB, Address, Home Phone > No, Mobile Phone No > > As one relation / table (There are several others in this db that are > not related to this issue) > > My problem is this > > I need to be able to select two dates on my webby so I have a range of > dates I wrote a function for me similar to your problem. You can visit it: http://a-kretschmer.de/tools/birthday.sql Maybe it is useful for you. It need one argument: a integer, and returns a list of people who has birthday between CURRENT_DATE and this+N days. Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Sunday 02 Oct 2005 4:02 am, brett wrote: > This is my first posting here, please forgive me if I make any > mistakes here. thought your question was dead simple. worked on the answer for hours at end. learnt a lot of sql, but couldnt solve it. Looks like a solution is out. All i can say is - we need more newbies like you! -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!