strange DATETIME field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Locked
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

strange DATETIME field

Post 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
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: strange DATETIME field

Post 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
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

Re: strange DATETIME field

Post 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
!!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: strange DATETIME field

Post 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.
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

Re: strange DATETIME field

Post 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"
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: strange DATETIME field

Post by pickle »

Hey now. :nono:

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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: strange DATETIME field

Post by Benjamin »

The solution is simple. Why not have a read? http://dev.mysql.com/doc/refman/5.0/en/datetime.html
BigJonMX
Forum Newbie
Posts: 15
Joined: Sun Nov 20, 2005 11:28 am
Location: CardboardBox, WindyCity

Re: strange DATETIME field

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: strange DATETIME field

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: strange DATETIME field

Post 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.
Locked