Re: SQL Date guru in the house?

Petar Nedyalkov wrote:

On Wednesday 11 May 2005 09:17, mwestern@sola.com.au wrote:

Hi All, I have a small problem. I have a project in which someone has got three integer fields for

holding the date.   DD, MM, YYYY in an sql database.    I now have to

have a page that inputs two dates and select records between those two dates. If I had a date field in the table it would be fairly simple, but I'm hoping to do this search/comparison without having to rewrite the pages/database that has already been designed. Start Date: 11/05/2005 End Date: 11/04/2005 SELECT * FROM blah WHERE mm BETWEEN 04 AND 05 AND dd BETWEEN 11 AND 11 AND yyyy BETWEEN 2005 AND 2005

Doesn't work for obvious reasons.      Is there any way that I can do

this date comparison I the SQL statement without having a decent date field?

Yes there's a way ;-) But you've mixed the month and day in the query. The standart textual format is MM/DD/YYYY :-) SELECT * FROM blah WHERE mm BETWEEN 11 AND 11 AND dd BETWEEN 4 AND 5 AND yyyy BETWEEN 2005 AND 2005 Look, there's a couple of standards. There's the US "standard" which you just pointed out MM/DD/YYYY, there's also the european standard which is DD/MM/YY (and seemingly australian aswell). There's also the RFC standard which is YYYYMMDD.

Well, back to the point. It doesn't work because with dates between M=1 and M=2, and D=1 and D=5, (US dates: 1/1/2005, and 2/5/2005), there aren't JUST the following days: 1/1/2005, 1/2/2005, 1/3/2005, 1/4/2005, 1/5/2005, 2/1/2005, 2/2/2005, 2/3/2005, 2/4/2005 and 2/5/2005. No! There's also dates in January that are beyond the 5th which are still before the 5th of February. That's the problem he's having. So I think you've misunderstood him.

My apologies as this is australian date format and this list is in the US I think? Regards Matthew