SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    E-business guru Eirik's Avatar
    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?
    Sincerely,

    Eirik Johansen
    Netmaking AS

  2. #2
    SitePoint Enthusiast lieblick's Avatar
    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"
    Vendor Ratings, Coupons & Specials:
    http://www.outcrier.com
    My Personal Site:
    http://www.epock.com

  3. #3
    SitePoint Author Kevin Yank's Avatar
    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 <= 1990
    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

  4. #4
    SitePoint Addict manipura's Avatar
    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?

  5. #5
    SitePoint Author Kevin Yank's Avatar
    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

  6. #6
    will code HTML for food Michel V's Avatar
    Join Date
    Sep 2000
    Location
    Corsica
    Posts
    552
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While we're asking for info, what's the difference between Datetime and Timestamp ?
    [blogger: zengun] [blogware contributor: wordpress]

  7. #7
    Database Jedi MattR's Avatar
    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.html

  8. #8
    SitePoint Author Kevin Yank's Avatar
    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

  9. #9
    Database Jedi MattR's Avatar
    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"...
    Egads! Another PHP + MySQL book!

    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.

  10. #10
    SitePoint Author Kevin Yank's Avatar
    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

  11. #11
    will code HTML for food Michel V's Avatar
    Join Date
    Sep 2000
    Location
    Corsica
    Posts
    552
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know about the others yet (not a book buyer when it comes to computers), but here's buying.
    [blogger: zengun] [blogware contributor: wordpress]

  12. #12
    SitePoint Author Kevin Yank's Avatar
    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

  13. #13
    E-business guru Eirik's Avatar
    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?
    Sincerely,

    Eirik Johansen
    Netmaking AS

  14. #14
    SitePoint Member
    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 ?

  15. #15
    SitePoint Author Kevin Yank's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •