Choosing rows with a date between two dates

[Eirik]

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?

[lieblick]

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"

[Kevin Yank]

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

[manipura]

Can you still query it like that if the 'date' field is a timestamp?

[Kevin Yank]

TIMESTAMP fields are stored in the format "YYYYMMDDhhmmss". Just adjust your query accordingly.

[Michel V]

While we're asking for info, what's the difference between Datetime and Timestamp ?

[MattR]

The almighty manual explains all:
http://www.mysql.com/doc/D/A/DATETIME.html

[Kevin Yank]

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.

[MattR]

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.

[Kevin Yank]

Here's hoping.

[Michel V]

I don't know about the others yet (not a book buyer when it comes to computers), but here's buying.

[Kevin Yank]

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.

[Eirik]

Thanks again so much, guys! Did I tell you all how much I love you lately?

[Micheal]

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 ?

[Kevin Yank]

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)

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	%