PHP mysql select date range

Question

I have a invoice type form submission and every submission has a date so I have created a varcha date field in mysql database to save the time.Time format is like '2011-06-26'.Now I need to select a range based on the date from my database.I tried following options but they don't display any result just blank page is displayed,even no errors.

Should I follow some special techniques on saving date to my database.If so please give me some explain about that because I am new to PHP and MYSQL development.

SELECT * FROM table_name 
WHERE 'date' 
BETWEEN UNIX_TIMESTAMP('2011-06-02') AND UNIX_TIMESTAMP('2011-06-25')




SELECT * FROM my_table 
WHERE 'date' 
BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)";

This is what I used to extract data

$result=mysql_query($query);
  while($row=mysql_fetch_array($result)){
      echo $row['tax']."<br>";
  }

Thank you.

MySQL actually has a "date" type, so you don't need to use VARCHAR to store your dates — Matt Healy
– Matt Healy, Commented Jun 26, 2011 at 8:20
@matthewh Ya I tried that also but still my result page is blank.Any ideas?. — Kanchana Randika
– Kanchana Randika, Commented Jun 26, 2011 at 8:23

dave · Accepted Answer · 2011-06-26 08:47:28Z

11

If you use DATETIME type to store your date the trick is simple. DATETIME allows you to store the date and the timestamp, however if only the date is specifed, timestamp will be stored as 00:00:00

I'll show you a simple example.

mysql> CREATE TABLE test_table(
       id INT UNSIGNED AUTO_INCREMENT,
       test_date DATETIME,
       PRIMARY KEY (id));

mysql> INSERT INTO test_table(test_date) 
       VALUES('2011-06-26'), ('2011-05-14'), ('2011-05-02');

mysql> SELECT * FROM test_table;
+----+---------------------+
| id | test_date           |
+----+---------------------+
|  1 | 2011-06-26 00:00:00 |
+----+---------------------+
|  2 | 2011-05-14 00:00:00 |
+----+---------------------+
|  3 | 2011-05-02 00:00:00 |
+----+---------------------+

mysql> SELECT * FROM test_table WHERE test_date
       BETWEEN '2011-05-01' AND '2011-05-31';
+----+---------------------+
| id | test_date           |
+----+---------------------+
|  2 | 2011-05-14 00:00:00 |
+----+---------------------+
|  3 | 2011-05-02 00:00:00 |
+----+---------------------+

That's it.

answered Jun 26, 2011 at 8:47

dave

2,2691 gold badge17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kanchana Randika Over a year ago

Thanx buddy.I tried but php result is still blank.This is my code of fetching data. $query="SELECT * FROM my_table WHERE date BETWEEN 2011-06-02 AND 2011-06-25"; $result=mysql_query($query); while($row=mysql_fetch_array($result)){ $data=$row["tax"]; echo $data; }

dave Over a year ago

You put the dates between quotes, isn't it? I'm trying it on PHP.

Dan Smith · Accepted Answer · 2011-06-26 08:21:56Z

2

If the date column is of the TIMESTAMP or DATETIME type then the query should be:

SELECT * FROM table_name 
WHERE 'date' 
BETWEEN '2011-06-02' AND '2011-06-25'

Edit: Sorry didn't see the column was a VARCHAR - you should change it to the DATETIME type to make things simpler.

answered Jun 26, 2011 at 8:21

Dan Smith

5,67534 silver badges33 bronze badges

1 Comment

Kanchana Randika Over a year ago

I tried your solution buddy.But still I am getting a blank page as the result.Now this is how my date data is appears in the database after I altered that to DATETIME. > 2011-06-22 00:00:00

user710818 · Accepted Answer · 2011-06-26 08:32:48Z

2

Name of column - or without or in backticks
If date is varchar - also need cast date SELECT * FROM my_table WHERE cast(date as DATE) BETWEEN CAST('2011-06-02' AS DATE) AND CAST('2011-06-25' AS DATE)";

answered Jun 26, 2011 at 8:32

user710818

24.4k59 gold badges153 silver badges209 bronze badges

Collectives™ on Stack Overflow

PHP mysql select date range

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related