MySQL WHERE dd/mm/yyyy

CrispWebdesign · #1 16-Oct-2008, 04:51

Hello,

Im trying to creat a report to show all enrties in my database entered in a specific month of a specific year. However my database stores the date in a colum called date_inserted in the format dd/mm/yyyy so i havent got a clue how to SELECT * from acct_quotes WHERE date_inserted = mm/yyyy???

Any ideas would be appreciated

Matt

admin · #2 16-Oct-2008, 09:46

Unless someone else has a better idea, I think you would have to use the MySQL date & time functions like YEAR() and MONTH().

Something like this:

Code:

... WHERE YEAR(`date_inserted`)=2008 AND MONTH(`date_inserted`)=10

CrispWebdesign · #3 16-Oct-2008, 09:53

Only problem with that is the date is more of a last modified date as it changes when the item is updated, Can i not just split the search by the "/"'s?

admin · #4 16-Oct-2008, 10:01

You can use strtotime() (PHP function) to get the timestamp. Use e.g. date("Y") to get the month and year numbers, and work from there. This is all off the top of my head. If you show some code, maybe I can suggest something better.

TurboPT · #5 16-Oct-2008, 20:21

Just for a quick check, although your date uses slashes, I tried this -- ignoring the day portion of the month:

Code:

// The sample data set
mysql> SELECT * FROM play1;
+----+------------+
| id | zdate      |
+----+------------+
|  1 | 2008-10-10 |
|  2 | 2008-09-16 |
|  3 | 2008-10-16 |
|  4 | 2008-07-16 |
|  5 | 2008-10-20 |
|  6 | 2008-06-16 |
+----+------------+
6 rows in set (0.00 sec)

// Query any day in October.
mysql> SELECT * FROM play1 WHERE zdate LIKE '2008-10-%';
+----+------------+
| id | zdate      |
+----+------------+
|  1 | 2008-10-10 |
|  3 | 2008-10-16 |
|  5 | 2008-10-20 |
+----+------------+
3 rows in set (0.00 sec)

// How about July only?
mysql> SELECT * FROM play1 WHERE zdate LIKE '2008-07-%';
+----+------------+
| id | zdate      |
+----+------------+
|  4 | 2008-07-16 |
+----+------------+
1 row in set (0.00 sec)

So, applying a slight modification, maybe this query might work for your purpose?:

Code:

SELECT * FROM acct_quotes WHERE date_inserted LIKE '%/mm/yyyy';

Of course, the mm/yyyy part will need real values.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Moving data from MySQL to MySQL	meurer	MySQL / PHP Forum	0	25-Jan-2007 10:03
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 03:54
CPanel 10, Unlimited Emails, Unlimited Subdomains, Unlimited MySQL. $2.00 per Month	Kalypsoweb	Web Hosting Advertisements & Offers	0	30-Oct-2005 01:51
Windows: From only £20p/y,Linux: from $10p/m. ASP, ASP.NET, PHP, Free MySQL, +More	EyotaHosts	Web Hosting Advertisements & Offers	0	28-Jun-2003 14:54

MySQL WHERE dd/mm/yyyy

Re: MySQL WHERE dd/mm/yyyy

Re: MySQL WHERE dd/mm/yyyy

Re: MySQL WHERE dd/mm/yyyy

Re: MySQL WHERE dd/mm/yyyy