Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #13408 > unrolled thread

select date from DATETIME field

Back to article view | Back to comp.lang.php

Contents

  select date from DATETIME field pentapus <pentapus@example.com> - 2014-04-19 15:39 -0400
    Re: select date from DATETIME field Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2014-04-19 15:50 -0400
      Re: select date from DATETIME field pentapus <pentapus@example.com> - 2014-04-19 17:44 -0400
        Re: select date from DATETIME field Luuk <luuk@invalid.lan> - 2014-04-20 15:13 +0200
    Re: select date from DATETIME field Tim Streater <timstreater@greenbee.net> - 2014-04-19 20:56 +0000
      Re: select date from DATETIME field The Natural Philosopher <tnp@invalid.invalid> - 2014-04-19 22:21 +0100
    Re: select date from DATETIME field Denis McMahon <denismfmcmahon@gmail.com> - 2014-04-19 21:01 +0000
      Re: select date from DATETIME field pentapus <pentapus@example.com> - 2014-04-19 17:39 -0400

#13408 — select date from DATETIME field

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

[toc] | [next] | [standalone]

#13409

On Saturday 19 April 2014 15:39, in comp.lang.php, "pentapus"
<pentapus@example.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

[toc] | [prev] | [next] | [standalone]

#13416

On 4/19/2014 3:50 PM, Lew Pitcher wrote:
> On Saturday 19 April 2014 15:39, in comp.lang.php, "pentapus"
> <pentapus@example.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

[toc] | [prev] | [next] | [standalone]

#13419

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@example.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.....

[toc] | [prev] | [next] | [standalone]

#13410

In article <liuje4$keu$1@news.albasani.net>, pentapus
<pentapus@example.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.

[toc] | [prev] | [next] | [standalone]

#13414

On 19/04/14 21:56, Tim Streater wrote:
> In article <liuje4$keu$1@news.albasani.net>, pentapus
> <pentapus@example.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.

[toc] | [prev] | [next] | [standalone]

#13413

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@gmail.com

[toc] | [prev] | [next] | [standalone]

#13415

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web