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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Beverley PortlockConnect With a Mentor Commented:
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Ray PaseurConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.