How to compare DateTime values?

Experts,

I am trying write a MySQL query to compare a static datetime value vs. the datetime values stored in my database. I am having trouble doing so.

The datetime values ("JobStartDate") in my database have the following format: '1899-12-30 xx:xx:xx'

The example query below should demonstrate what I am trying to do:

$query = "SELECT * FROM table WHERE JobStartDate < '1899-12-30 09:59:00'";

Can someone please assist me in correcting my syntax so that the two datetime values are evaluated correctly?
evibesmusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
The basics of your query look OK. I cannot see an obvious syntactic error. Are you getting an error message that leads you to believe your syntax is wrong?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
evibesmusicAuthor Commented:
@Beverley Portlock:

I am not getting an error but, I am not getting any results from the query when I know results should exist.
0
Beverley PortlockCommented:
Can you post some sample data and the fragment of PHP/MySQL code that is not working as you expect?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Ray PaseurCommented:
This article shows some of the ways of dealing with DATETIME values in PHP and MYSQL.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Key points: All date / time values for internal use should be held in ISO-8601 format.  In the MySQL column definition, this is data type DATETIME.  In PHP this is date('c') or date('Y-m-d H:i:s').  The format is both easy to read and well-standardized allowing comparison and sorting.

If you're using the BETWEEN clause in a query, you may want to be aware of this:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_11210-Beware-of-Between.html
0
evibesmusicAuthor Commented:
@all:

The column in my database was defined as a "text" data type and not a "datetime" data type. I've updated the data type and now my query works.

Cheers!
0
Beverley PortlockCommented:
Thank you. I am glad you got it sorted.
0
Ray PaseurCommented:
Bingo!  Glad it's pointed in the right direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.