strange DATETIME field
Moderator: General Moderators
strange DATETIME field
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
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
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Re: strange DATETIME field
you have to convert your string to MySQL DATE format, and compare that against the DATE part of your DATETIME column, thusly:
See STR_TO_DATE() and DATE()
Also might be helpful to read up on the differences between DATE, TIMESTAMP and DATETIME column types
Code: Select all
SELECT * FROM mytable WHERE STR_TO_DATE('2008/06/19', '%Y/%m/%d') = DATE(stamp);Also might be helpful to read up on the differences between DATE, TIMESTAMP and DATETIME column types
Re: strange DATETIME field
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
!!
Your suggestion gives ZERO results
whereas
SELECT * FROM mytable WHERE stamp='19/06/2008 10:50:36'
gives one result
!!
Re: strange DATETIME field
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.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
!!
Re: strange DATETIME field
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"
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
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.
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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Re: strange DATETIME field
The solution is simple. Why not have a read? http://dev.mysql.com/doc/refman/5.0/en/datetime.html
Re: strange DATETIME field
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.
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
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: strange DATETIME field
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.
BigJonMX, you know.. you can actually PAY people to do this if you aren't happy with our the expertise our FREE VOLUNTEER COMMUNITY.