SQL,PHP: Converting Date Format

Question

I'm having a bit of a problem where both the SQL function and the php function does not convert dates properly when they're in the format of "DD-MM-YYYY". I am using a mysql database and I want to convert the date to "YYYY-MM-DD" before it is entered into the database.

The SQL function:

INSERT INTO lease 
VALUES(3, 4, STR_TO_DATE('22-02-2015', '%Y-%m-%d'),    STR_TO_DATE('27-02-2015', '%Y-%m-%d'))

Returns as "2022-02-20" and "2027-02-20"

Also, the php function puts the month and day in the wrong place so I have to do "Y-d-m" instead, like so:

$startdate = date("Y-d-m", strtotime('27-02-2015'));
$enddate = date("Y-d-m", strtotime('23-02-2015'));

Although that stores it correctly. Any date where the day is > 12 will reset the date to"1970-01-01" because it thinks the day is the month.

Can anyone help with this?

score 1 · Accepted Answer · answered Feb 27 '15 at 16:40

1

The format argument for STR_TO_DATE() is the format used for the string argument

So use

STR_TO_DATE('22-02-2015', '%d-%m-%Y'),

answered Feb 27 '15 at 16:40

Mark Baker

209,507
32
346
385

1

Wow, you literally answered that in under 2 min. It took me that long to read it! – user1717828 Feb 27 '15 at 16:42
Thank you so much! Rookie mistake by me. – Tom Nulty Feb 27 '15 at 16:44

SQL,PHP: Converting Date Format

1 Answers1