MySQL, converting mm/md/yy to DATE

Forum Moderators: open

Message Too Old, No Replies

MySQL, converting mm/md/yy to DATE

csdude55

9:10 pm on Sep 13, 2021 (gmt 0)

I have a series of about 200 rows with dates that look like:

6/8/19
12/7/19
12/14/20

and so on.

I need to put these in MySQL with a "timestamp" format (which I chose because I needed to default to today's date, and can't find an option for JUST the date).

I'm pretty sure that I need to use DATE_FORMAT, but I can't pin down the syntax. Can you guys and gals suggest the proper way?

Also, can I use it once while defining the columns, or do I have to use it in each row? Eg, can I do this?

INSERT INTO table (id, DATE_FORMAT(something, something, postdate), value) VALUES (...)

Or does it have to be like:

INSERT INTO table (id, postdate, value) VALUES
('1', DATE_FORMAT(something, something, '6/8/19'), 'foo'),
('2', DATE_FORMAT(something, something, '12/7/19'), 'bar'),
('3', DATE_FORMAT(something, something, '12/14/19'), 'whatever')

phranque

1:27 am on Sep 14, 2021 (gmt 0)

any date literal accepted by mysql will require a year-first format and two-digit date literals have the obvious usual problems.

your solution will include fixing the date literal in perl or whatever the application script is written in before handing off the date or timestamp literal value to mysql.

csdude55

5:33 pm on Sep 14, 2021 (gmt 0)

Blah :-( I was just inserting manually, so I wasn't planning to use PHP or Perl.

Knowing this, though, I was able to copy it to Excel, modify the date format, then copy it back to Notepad. I'm going to have to do this 12 more times, so it's a pain but not a tragedy.

Thanks for saving me time trying to figure out an alternative, though! LOL

phranque

8:41 pm on Sep 14, 2021 (gmt 0)

you could easily do this using the unix sed command

federicorazzoli

1:29 pm on Oct 23, 2021 (gmt 0)

I just want to post an update for those who read this table now-ish. The question was from 2013, and actually there was no way to default a DATE column to the current date. Nowadays you can use DEFAULT DATE(). This is supported since MariaDB 10.2 and MySQL 8.