SitePoint Sponsor |
|
User Tag List
Results 1 to 15 of 15
-
Jun 20, 2001, 08:28 #1
- Join Date
- Nov 2000
- Location
- Oslo, Norway
- Posts
- 413
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Choosing rows with a date between two dates
Hi,
I want to fetch all the rows with a date between one date and another. However, the following SQL SELECT query does not work as I've hoped (in fact, it does not work at all):
SELECT Title FROM Activity WHERE Date >= 2001-02-01 AND Date <= 2001-10-01
Why is that?
-
Jun 20, 2001, 08:41 #2
- Join Date
- Jun 2001
- Location
- Tallahassee, FL
- Posts
- 98
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Does SQL give you an error message?
Assuming there is a field in the DB called "Date" and it stores a character value of "2001-02-01" ... perhaps you just need to double or single quote the string you're looking for ..
IE:
SELECT Title FROM Activity WHERE Date >= "2001-02-01" AND Date <= "2001-10-01"
-
Jun 20, 2001, 09:04 #3
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
The lack of quotes was definitely the problem here. Without quotes, MySQL takes the '-'s to mean that you want to perform a subtraction, so what your query was actually saying was:
SELECT Title FROM Activity WHERE Date >= 1998 AND Date <= 1990Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Jun 20, 2001, 09:43 #4
- Join Date
- Apr 2001
- Location
- Calgary,AB
- Posts
- 345
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Can you still query it like that if the 'date' field is a timestamp?
-
Jun 20, 2001, 10:53 #5
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
TIMESTAMP fields are stored in the format "YYYYMMDDhhmmss". Just adjust your query accordingly.
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Jun 20, 2001, 11:33 #6
-
Jun 20, 2001, 11:48 #7
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
The almighty manual explains all:
http://www.mysql.com/doc/D/A/DATETIME.htmlMatt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 20, 2001, 15:42 #8
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
And from Appendix C of the upcoming book version of "Building a Database-Driven Website with PHP and MySQL"...
DATETIME
Description: A date and time.
Range: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
Storage space: 8 bytes (64 bits)
TIMESTAMP[(M)]
Description: A timestamp (date/time), in YYYYMMDDHHMMSS format.
Range: 19700101000000 to sometime in 2037 on current systems.
Storage space: 4 bytes (32 bits)
Notes: An INSERT or UPDATE operation on a row containing one or more TIMESTAMP columns will automatically update the first TIMESTAMP column in the row with the current date/time. This lets you use such a column as the 'last modified date/time' for the row. Assigning a value of NULL to the column will have the same effect, thereby providing a means of 'touching' the date/time. You can also assign actual values as you would for any other column.
Allowable values for M are 14, 12, 10, 8, 6, 4, and 2, and correspond to the display formats YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYMMDDHHMM, YYYYMMDD, YYMMDD, YYMM, and YY respectively. Odd values from 1 to 13 will automatically be bumped up to the next even number, while values of 0 or greater than 14 are changed to 14.Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Jun 20, 2001, 15:57 #9
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally posted by kyank
And from Appendix C of the upcoming book version of "Building a Database-Driven Website with PHP and MySQL"...
Even though the market is saturated there are still enough people who don't have one yet. Best of luck; I'm sure it'll do well.Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Jun 20, 2001, 16:40 #10
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Here's hoping.
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Jun 20, 2001, 16:52 #11
-
Jun 20, 2001, 17:02 #12
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
Woo hoo!
For those who are curious, the book version contains the fully revised and updated edition of the article series (I've been working on it for a month!), and two additional chapters that will not be appearing online. As of right now, those chapters will be covering Sessions/Cookies and storing/retrieving binary data in MySQL, but that may still change. To top it off, there will be extremely handy reference appendices documenting MySQL syntax, MySQL functions, MySQL column types, and PHP's MySQL functions.
Price yet to be set, but you can pretty much count on it not being another $65 book you'll never read all the way through. We are also considering offering it as an e-book in PDF format for a smaller price.Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
-
Jun 21, 2001, 00:13 #13
- Join Date
- Nov 2000
- Location
- Oslo, Norway
- Posts
- 413
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks again so much, guys! Did I tell you all how much I love you lately?
-
Jun 23, 2001, 06:00 #14
- Join Date
- Jun 2001
- Posts
- 22
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Is there a function to change the format of a DATETIME field to another date format like " Thursday 12th September 2001 12:24 pm"
I have read the date() function can be used to format the date. But the date() function needs a timestamp as the argument.
Is there any function for formatting date which takes the argument as a DATETIME variable ?
-
Jun 23, 2001, 09:34 #15
- Join Date
- Apr 2000
- Location
- Melbourne, Australia
- Posts
- 2,571
- Mentioned
- 2 Post(s)
- Tagged
- 0 Thread(s)
MySQL's DATE_FORMAT function does exactly this. Again, from the reference section of the upcoming book (apologies for the poorly formatted table -- I'm in a bit of a hurry):
DATE_FORMAT(date,format)
Takes the date or time value date and returns it formatted according to the formatting string format, which may contain any of the following symbols as placeholders shown in Table 2.
Table 2 – DATE_FORMAT() symbols (example: 2001-01-01 01:00:00)Code:Symbol Displays Example %M Month name January %W Weekday name Monday %D Day of the month with English suffix 1st %Y Year, numeric, 4 digits 2001 %y Year, numeric, 2 digits 01 %X Year for the week where Sunday is the 1st day of the week, 4 digits (use with %V) 2001 %x Like %X, Monday 1st day of the week (%v) 2001 %a Abbreviated weekday name Mon %d Day of the month 01 %e Day of the month 1 %m Month of the year, numeric 01 %c Month of the year, numeric 1 %b Abbreviated month name Jan %j Day of the year 001 %H Hour of the day (24-hour format, 00-23) 01 %k Hour of the day (24-hour format, 0-23) 1 %h Hour of the day (12-hour format, 01-12) 01 %I Hour of the day (12-hour format, 01-12) 01 %l Hour of the day (12-hour format, 1-12) 1 %i Minutes 00 %r Time, 12-hour (hh:mm:ss: AM/PM) 01:00:00 AM %T Time, 24-hour (hh:mm:ss) 01:00:00 %S Seconds 00 %s Seconds 00 %p AM or PM AM %w Day of the week, numeric (0=Sunday) 1 %U Week (00-53), Sunday 1st day of the week 01 %u Week (00-53), Monday 1st day of the week 01 %V Week (1-53), Sunday 1st day of week (%X) 01 %v Week (1-53), Monday 1st day of week (%x) 01 %% An actual percent sign %
Kevin Yank
CTO, sitepoint.com
I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
Baby’s got back—a hard back, that is: The Ultimate CSS Reference
Bookmarks