Mysql date format to match mysql records

[johnCash]

I want to compare the date in mysql to some other date. Rather can formatting all the value in the database records to a specific date format and comparing it to a date value in where clause, I would like to keep the records in the format as they are and convert comparison date to the format that match the mysql record’s date format.

eg.
MySQL Record’s Date Format (for field created_at):
2007-11-24 17:50:27.0

Where clause Date format is:
YYYYMMDDHHMMSS

So, I need to make the where clause date format to mysql date format. How do I turn YYYYMMDDHHSS to YYYY-MM-DD HH:MM:SS.0? Also, I don’t know how to get SS.0?

Tried this in where clause, this wasn’t doing any good:
created_at = DATE_FORMAT(“%Y-%M-%d %H:%i:%s”,“200711070000”)

[r937]

if the created_at column is defined as DATETIME then you just have to make sure that the value you want to compare it to is given in a way that mysql will recognize it as a datetime value

so WHERE created_at = 20071107000000

@daManual

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

• As a string in either ‘YYYY-MM-DD HH:MM:SS’ or ‘YY-MM-DD HH:MM:SS’ format. A ``relaxed’’ syntax is allowed: Any punctuation character may be used as the delimiter between date parts or time parts. For example, ‘98-12-31 11:30:45’, ‘98.12.31 11+30+45’, ‘98/12/31 113045’, and ‘98@12@31 11^30^45’ are equivalent.
• As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format. A ``relaxed’’ syntax is allowed here, too. For example, ‘98-12-31’, ‘98.12.31’, ‘98/12/31’, and ‘98@12@31’ are equivalent.
• As a string with no delimiters in either ‘YYYYMMDDHHMMSS’ or ‘YYMMDDHHMMSS’ format, provided that the string makes sense as a date. For example, ‘19970523091528’ and ‘970523091528’ are interpreted as ‘1997-05-23 09:15:28’, but ‘971122129015’ is illegal (it has a nonsensical minute part) and becomes ‘0000-00-00 00:00:00’.
• As a string with no delimiters in either ‘YYYYMMDD’ or ‘YYMMDD’ format, provided that the string makes sense as a date. For example, ‘19970523’ and ‘970523’ are interpreted as ‘1997-05-23’, but ‘971332’ is illegal (it has nonsensical month and day parts) and becomes ‘0000-00-00’.
• As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as ‘1983-09-05 13:28:00’.
• As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as ‘1983-09-05’.
• As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

however, you do have another problem

if you want all the rows for a specific date, and your column values include a time portion, then you will need to do something like this:

WHERE created_at >= '2007-11-07'
  AND created_at  < '2007-11-08'

[johnCash]

@r937

however, you do have another problem

if you want all the rows for a specific date, and your column values include a time portion, then you will need to do something like this:

WHERE created_at >= '2007-11-07'
  AND created_at  < '2007-11-08'

What if I want to do datetime comparison like:

created_at <= 20071231085434

I tried this, I don’t think it works.

[r937]

how do you know it doesn’t work? it is one of the accceptable formats

me, i always use strings – ‘2007-12-31 08:54:34’

[johnCash]

It works. I was missing couple of extra digits at the end.