FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » select date from DATETIME field
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
select date from DATETIME field [message #185599] Sat, 19 April 2014 15:39 Go to next message
pentapus is currently offline  pentapus
Messages: 3
Registered: April 2014
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 15:50 Go to previous messageGo to next message
Lew Pitcher is currently offline  Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
Member
add to buddy list
ignore all messages by this user
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 16:00 Go to previous messageGo to next message
Tim Streater is currently offline  Tim Streater
Messages: 328
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 17:01 Go to previous messageGo to next message
Denis McMahon is currently offline  Denis McMahon
Messages: 634
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 17:21 Go to previous messageGo to next message
The Natural Philosoph is currently offline  The Natural Philosoph
Messages: 993
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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 17:39 Go to previous messageGo to next message
pentapus is currently offline  pentapus
Messages: 3
Registered: April 2014
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 17:44 Go to previous messageGo to next message
pentapus is currently offline  pentapus
Messages: 3
Registered: April 2014
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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 09:13 Go to previous message
Luuk is currently offline  Luuk
Messages: 329
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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.....
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: PHP Parse error: syntax error, unexpected '$sql' (T_VARIABLE) in
Next Topic: Use PHP to populate a Mailing list from a webpage
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Oct 21 02:58:21 EDT 2017

Total time taken to generate the page: 0.00810 seconds