Query for DateTime field, MySQL DB

[aewhale]

Is there a way to query only the Date section for the field?

I would like to construct a query that reports the records which are created on a specific day. Using the PHPMyAdmin tool I can build the following query:

SELECT *
FROM `relaytofrom`
WHERE `origin_type` = CONVERT( _utf8 'MANUAL'
USING latin1 )
COLLATE latin1_swedish_ ci
AND `create_time` = '2008-05-28 08:19:05'

(another question, why on earth can't I build a SIMPLE non-converting query against the DB???)

The problem that I have is that there is always a Time included in the DateTime query string. If I eliminate the time section of the query string. I do not retrieve any records in the select.

Is there a way to query only the Date section for the field?

[code green]

another question, why on earth can't I build a SIMPLE non-converting query against the DB

PHPmyAdmin does not have a query building tool.
It is not Access.
The authors simply believe it is useful to display the query required to output a page of data.

AND `create_time` = '2008-05-28 08:19:05'
If I eliminate the time section of the query string. I do not retrieve any records in the select.

Of course not.
The time would have to match exactly.
What about

BETWEEN  date.00:00 AND date.23:59?

[aewhale]

What I Really want to be able to do is to query just using the date, is this not possible?

BTW, I am guessing that your suggestion would probably work for a script to report the information. But my problem with the BETWEEN option is that I would have to scan the entire DB.

[aewhale]

PHPmyAdmin does not have a query building tool.
It is not Access.

I just reread this reply. WOW, you must be kidding me! Anyone posting to the MySQL is not necessary an Access hack. In fact, I have 20+ years of Unix/Linux expertise, I just did not have another DB Resource at the time to query my question to. Thanks, but no thanks, I gave up Windows apps a Looong time ago.

[code green]

I didn't mean to offend.
I was using an example (and I poorly chose Access)
to point out that the phpMyAdmin Search tool is not really a query building tool.
I use phpMyAdmin (in Windows),
I think the CONVERT function is used to format the output on the web page

BTW, I am guessing that your suggestion would probably work for a script to report the information. But my problem with the BETWEEN option is that I would have to scan the entire DB

I don't quite understand what you mean by scan the entire DB
Alternatively use

AND `create_time` = DATE('2008-05-28 08:19:05')

I was a little bit slow in my first post suggesting BETWEEN.
That is more a T-SQL solution

[aewhale]

OK, no offense taken. I really hate the Covert function in phpMyAdmin.

I find it a useful tool, but a little over simplified sometimes.

If I put a select clause with the time, it will specifically look for that time. My problem is what selection syntax will be effective for selecting all of the records on a given day?

[code green]

Both examples I have given you can be made to work.
This is the best

AND `create_time` = DATE('2008-05-28 08:19:05')

This will extract the date portion from a DATETIME value within the brackets.
So you need something like

AND DATE(`create_time`) = '2008-05-28'

[aewhale]

Both examples I have given you can be made to work.
This is the best

AND `create_time` = DATE('2008-05-28 08:19:05')

This will extract the date portion from a DATETIME value within the brackets.
So you need something like

AND DATE(`create_time`) = '2008-05-28'

Excellent! It works perfectly.

[aewhale]

Well, it works for SQL but here is what I have in the Perl Report program:

# Report how many rows were created
my $rows = $dbh->do("SELECT Count() relaytofrom WHERE origin_type = 'MANUAL' and create_time = NOW() - INTERVAL 1 DAY ");
print "$rows Created Manual connections for Email\n";

I get the following:

DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') relaytofrom WHERE origin_type = 'MANUAL' and create_time = NOW at ./db_maintenance. pl line 164, <INFILE> line 161.

What is the NOW() Function?