| select date from DATETIME field [message #185599] |
Sat, 19 April 2014 19:39  |
pentapus
Messages: 3
Registered: April 2014
Karma: 0
|
Junior Member |
|
|
I have a DATETIME field that I am looking for rows that just match the
date. What is the easy way?
I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
no go...
--
pentapus
|
|
|
|
| Re: select date from DATETIME field [message #185600 is a reply to message #185599] |
Sat, 19 April 2014 19:50  |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
On Saturday 19 April 2014 15:39, in comp.lang.php, "pentapus"
<pentapus(at)example(dot)com> wrote:
> I have a DATETIME field that I am looking for rows that just match the
> date. What is the easy way?
>
> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
> no go...
Try quoting the target date, as in
SELECT * from some_table WHERE DATE(time-date-field) = '2014-01-01';
Here's an example from something I'm working on...
mysql> select recipe_name, recipe_date from recipe where date(recipe_date)
= '2014-04-14';
+--------------------+---------------------+
| recipe_name | recipe_date |
+--------------------+---------------------+
| Beef and Corn Bake | 2014-04-14 20:27:13 |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> select recipe_name, recipe_date from recipe where date(recipe_date)
= '2014-04-03';
+---------------------+---------------------+
| recipe_name | recipe_date |
+---------------------+---------------------+
| Coffee Rubbed Steak | 2014-04-03 09:14:20 |
+---------------------+---------------------+
1 row in set (0.00 sec)
HTH
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
|
|
|
|
| Re: select date from DATETIME field [message #185601 is a reply to message #185599] |
Sat, 19 April 2014 20:00 |
Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
In article <liuje4$keu$1(at)news(dot)albasani(dot)net>, pentapus
<pentapus(at)example(dot)com> wrote:
> I have a DATETIME field that I am looking for rows that just match the
> date. What is the easy way?
>
> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
> no go...
Have you seen the doctor about this problem, or has your brain already
been amputated?
What OS?
What version of PHP?
What database?
and so on.
--
"I am enclosing two tickets to the first night of my new play; bring a
friend.... if you have one." - GB Shaw to Churchill "Cannot possibly
attend first night, will attend second... if there is one." - Winston
Churchill, in response.
|
|
|
|
| Re: select date from DATETIME field [message #185604 is a reply to message #185599] |
Sat, 19 April 2014 21:01 |
Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Sat, 19 Apr 2014 15:39:52 -0400, pentapus wrote:
> I have a DATETIME field that I am looking for rows that just match the
> date. What is the easy way?
>
> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
> no go...
I thought this was a php newsgroup, you seem to have confused it with
comp.databases.[something] or possibly alt.php.sql
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
| Re: select date from DATETIME field [message #185605 is a reply to message #185601] |
Sat, 19 April 2014 21:21 |
The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 19/04/14 21:56, Tim Streater wrote:
> In article <liuje4$keu$1(at)news(dot)albasani(dot)net>, pentapus
> <pentapus(at)example(dot)com> wrote:
>
>> I have a DATETIME field that I am looking for rows that just match the
>> date. What is the easy way?
>>
>> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ...
>> but no go...
>
> Have you seen the doctor about this problem, or has your brain already
> been amputated?
>
> What OS?
> What version of PHP?
> What database?
>
> and so on.
>
Do you really think it makes a difference?
--
Ineptocracy
(in-ep-toc’-ra-cy) – a system of government where the least capable to
lead are elected by the least capable of producing, and where the
members of society least likely to sustain themselves or succeed, are
rewarded with goods and services paid for by the confiscated wealth of a
diminishing number of producers.
|
|
|
|
| Re: select date from DATETIME field [message #185606 is a reply to message #185604] |
Sat, 19 April 2014 21:39 |
pentapus
Messages: 3
Registered: April 2014
Karma: 0
|
Junior Member |
|
|
On 4/19/2014 5:01 PM, Denis McMahon wrote:
> On Sat, 19 Apr 2014 15:39:52 -0400, pentapus wrote:
>
>> I have a DATETIME field that I am looking for rows that just match the
>> date. What is the easy way?
>>
>> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
>> no go...
>
> I thought this was a php newsgroup, you seem to have confused it with
> comp.databases.[something] or possibly alt.php.sql
Indeed. I sent this off when quickly when I had company at the door. It
should have gone elsewhere. My bad. I did not know of alt.php.sql, I
intended comp.databases.mysql
--
pentapus
|
|
|
|
| Re: select date from DATETIME field [message #185607 is a reply to message #185600] |
Sat, 19 April 2014 21:44 |
pentapus
Messages: 3
Registered: April 2014
Karma: 0
|
Junior Member |
|
|
On 4/19/2014 3:50 PM, Lew Pitcher wrote:
> On Saturday 19 April 2014 15:39, in comp.lang.php, "pentapus"
> <pentapus(at)example(dot)com> wrote:
>
>> I have a DATETIME field that I am looking for rows that just match the
>> date. What is the easy way?
>>
>> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
>> no go...
>
> Try quoting the target date, as in
> SELECT * from some_table WHERE DATE(time-date-field) = '2014-01-01';
Thanks. That works. I'd left out the dashes (in my php date code) when I
changed to DATETIME. Just knowing I had the sql syntax correct was
enough to put me on the right track.
>
> Here's an example from something I'm working on...
>
> mysql> select recipe_name, recipe_date from recipe where date(recipe_date)
> = '2014-04-14';
> +--------------------+---------------------+
> | recipe_name | recipe_date |
> +--------------------+---------------------+
> | Beef and Corn Bake | 2014-04-14 20:27:13 |
> +--------------------+---------------------+
> 1 row in set (0.00 sec)
>
> mysql> select recipe_name, recipe_date from recipe where date(recipe_date)
> = '2014-04-03';
> +---------------------+---------------------+
> | recipe_name | recipe_date |
> +---------------------+---------------------+
> | Coffee Rubbed Steak | 2014-04-03 09:14:20 |
> +---------------------+---------------------+
> 1 row in set (0.00 sec)
>
>
> HTH
It did.
>
--
pentapus
|
|
|
|
| Re: select date from DATETIME field [message #185610 is a reply to message #185607] |
Sun, 20 April 2014 13:13 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 19-4-2014 23:44, pentapus wrote:
> On 4/19/2014 3:50 PM, Lew Pitcher wrote:
>> On Saturday 19 April 2014 15:39, in comp.lang.php, "pentapus"
>> <pentapus(at)example(dot)com> wrote:
>>
>>> I have a DATETIME field that I am looking for rows that just match the
>>> date. What is the easy way?
>>>
>>> I thought SELECT * FROM some_table WHERE DATE(time-date-field) = ... but
>>> no go...
>>
>> Try quoting the target date, as in
>> SELECT * from some_table WHERE DATE(time-date-field) = '2014-01-01';
>
> Thanks. That works. I'd left out the dashes (in my php date code) when I
> changed to DATETIME. Just knowing I had the sql syntax correct was
> enough to put me on the right track.
>>
This would be better:
SELECT * FROM some_table WHERE time-date-field between '2014-04-20' and
date('2014-04-20') + INTERVAL 1 DAY;
Because your database can make use of the index you defined on that
field.....
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]