CODEWITHSUNDEEP
mysqldate
blankip
blankip

Reputation: 340

How do you deal with a non-standard date format in mysql?

I am getting a report with a few fields that are set at m/d/y hh:mm (so yes one digit for one digit months). I will not be importing these in the future so I would rather not rely on changing the input file. How can mysql adjust to that format so that I can do date lookups and time based queries?

Example:

table a -

empID

lastlogin

How do I search for employees that have logged in this month using the lastlogin field as the date field described above?

Upvotes: 0

Views: 199

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

The MySQL function str_to_date() is pretty flexible. Even though it seems like it prefers MM/DD/YYYY formats, it does work with M/D/YYYY.

So you can do:

select a.*
from a
where str_to_date(lastlogin, '%m/%d/%Y') >= now() - interval 30 days;

However, you shouldn't store dates in the database as strings. It is a much better idea to store them as actual date or datetime fields. You can do the transformation once, when loading the data, and then not have to worry about the data.

Upvotes: 1

Related Questions