Page 1 of 1
strange DATETIME field
Posted: Sun Jun 22, 2008 7:25 am
by BigJonMX
i seem to have got into a terrible mess with my dates. Can anyone advise...
i have a table with a DATETIME field "stamp", holding values like
19/06/2008 10:50:36
if i do
SELECT * FROM mytable
i get 84 records = correct
if i do
SELECT * FROM mytable WHERE stamp="2008/06/19"
i get NO records (i should get 1 record)
i tried...
stamp='2008/06/19'
DATE(stamp)='2008/06/19'
stamp=DATE('2008/06/19')
and the reverse... 19/06/2008
and even... DAY(stamp)='19'
stamp=DAY(19)
stamp=DAY('19')
= FAILS
Please anyone. i am pulling out all my hair !
version=
5.0.24a-Debian_2.dotdeb.0-log
Re: strange DATETIME field
Posted: Sun Jun 22, 2008 7:43 am
by Kieran Huggins
you have to convert your string to MySQL DATE format, and compare that against the DATE part of your DATETIME column, thusly:
Code: Select all
SELECT * FROM mytable WHERE STR_TO_DATE('2008/06/19', '%Y/%m/%d') = DATE(stamp);
See
STR_TO_DATE() and
DATE()
Also might be helpful to read up on the differences between
DATE, TIMESTAMP and DATETIME column types
Re: strange DATETIME field
Posted: Sun Jun 22, 2008 8:00 am
by BigJonMX
Thanks for the reply, but it still doesnt work.
Your suggestion gives ZERO results
whereas
SELECT * FROM mytable WHERE stamp='19/06/2008 10:50:36'
gives one result
!!
Re: strange DATETIME field
Posted: Sun Jun 22, 2008 11:54 am
by califdon
BigJonMX wrote:Thanks for the reply, but it still doesnt work.
Your suggestion gives ZERO results
whereas
SELECT * FROM mytable WHERE stamp='19/06/2008 10:50:36'
gives one result
!!
Notice the difference in format between what you are doing (dd/mm/yyyy) and what Kieran suggested (yyyy/mm/dd). You can use any format you want, but you have to be consistent throughout your script logic.
Re: strange DATETIME field
Posted: Tue Jun 24, 2008 1:47 pm
by BigJonMX
Notice the difference between what people actually read and what they assume!!
What i tried is what was suggested - and it didnt work.
(to assume i typed something else, without any evidence, is just wasting everyones time)
"But thanks for playing"
Re: strange DATETIME field
Posted: Tue Jun 24, 2008 2:21 pm
by pickle
Hey now.
He was being polite. You were not. If you're frustrated, that's fine - no need to be rude to people who are
volunteering to
help you in their
free time.
Re: strange DATETIME field
Posted: Tue Jun 24, 2008 2:29 pm
by Benjamin
Re: strange DATETIME field
Posted: Sat Jun 28, 2008 1:10 pm
by BigJonMX
Of course i read the manual.
Do you get a medal for quantity of posts?
Dont worry your pretty little head about it anymore.
For those that are interested, there is a bug showing up with a specific combination of software.#
CLOSED.
Re: strange DATETIME field
Posted: Sat Jun 28, 2008 1:33 pm
by Benjamin
The solution is simple. I didn't provide it to you because of your attitude. If you're going to be ornery all the time you might as well find another place to ask questions.
Re: strange DATETIME field
Posted: Sat Jun 28, 2008 1:48 pm
by John Cartwright
A wise once said, don't bite the hand that feeds you..
BigJonMX, you know.. you can actually PAY people to do this if you aren't happy with our the expertise our FREE VOLUNTEER COMMUNITY.