Re: [PHP] SQL Date guru in the house?
On Wednesday 11 May 2005 09:17, [EMAIL PROTECTED] 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, 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 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/ :-) SELECT * FROM blah WHERE mm BETWEEN 11 AND 11 AND dd BETWEEN 4 AND 5 AND BETWEEN 2005 AND 2005 My apologies as this is australian date format and this list is in the US I think? Regards Matthew -- Cyberly yours, Petar Nedyalkov Devoted Orbitel Fan :-) PGP ID: 7AE45436 PGP Public Key: http://bu.orbitel.bg/pgp/bu.asc PGP Fingerprint: 7923 8D52 B145 02E8 6F63 8BDA 2D3F 7C0B 7AE4 5436 pgpqYbi9Bu88s.pgp Description: PGP signature
RE: [PHP] SQL Date guru in the house?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 May 2005 07:17 To: php-general@lists.php.net Subject: [PHP] SQL Date guru in the house? 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, 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. - I strongly recommend that you rewrite the application to use dates! Can you at least add a column to the table which makes a datetime out of those three fields? Then you can do your date comparisons easily. Which database are you using? - 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 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? My apologies as this is australian date format and this list is in the US I think? No need to apologise - this list is on the internet and has posters from all over the world --- Regards Matthew Gamma Global : Suppliers of HPCompaq, IBM, Acer, EPI, APC, Cyclades, D-Link, Cisco, Sun Microsystems, 3Com GAMMA GLOBAL (UK) LTD IS A RECOGNISED 'INVESTOR IN PEOPLE' AND AN 'ISO 9001 2000' REGISTERED COMPANY ** CONFIDENTIALITY NOTICE: This Email is confidential and may also be privileged. If you are not the intended recipient, please notify the sender IMMEDIATELY; you should not copy the email or use it for any purpose or disclose its contents to any other person. GENERAL STATEMENT: Any statements made, or intentions expressed in this communication may not necessarily reflect the view of Gamma Global (UK) Ltd. Be advised that no content herein may be held binding upon Gamma Global (UK) Ltd or any associated company unless confirmed by the issuance of a formal contractual document or Purchase Order, subject to our Terms and Conditions available from http://www.gammaglobal.com EOE ** ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Date guru in the house?
Matthew, Depending on what database, you should have a TO_DATE() or DATE() function that you can cast your dd/mm/ to a date field in the select statement. select to_date(, mm, dd) mydate from dual where mydate = '11/05/2005' and mydate = '11/04/2005' Look in the help files or the manual for the correct syntax/function for the database your using. HTH -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 11 May 2005 07:17 To: php-general@lists.php.net Subject: [PHP] SQL Date guru in the house? 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, 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 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? My apologies as this is australian date format and this list is in the US I think? Regards Matthew This message has been delivered to the Internet by the Revenue Internet e-mail service * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Date guru in the house?
Petar Nedyalkov wrote: On Wednesday 11 May 2005 09:17, [EMAIL PROTECTED] 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, 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 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/ :-) SELECT * FROM blah WHERE mm BETWEEN 11 AND 11 AND dd BETWEEN 4 AND 5 AND BETWEEN 2005 AND 2005 Look, there's a couple of standards. There's the US standard which you just pointed out MM/DD/, there's also the european standard which is DD/MM/YY (and seemingly australian aswell). There's also the RFC standard which is MMDD. 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
