Myphpadmin - DD MM YYYY ?

[Barny74]

 

Anyone know how to get my database to recognise the date as DDMMYYYY , cant upload date as DDMMYYYY as it wants everything as YYYYMMDD.

[requinix]

You don't. MySQL wants YYYYMMDD format, with optional separators, and either a 2- or 4-digit year.

 

Why can't you use that format?

[Barand]

The correct format for MySql dates is YYYY-MM-DD. The format you are trying to save is useless (cannot be compared, cannot be sorted and cannot be used directly with datetime functions)

 

Specify your date column as type DATE. Convert to the correct format either before you insert, or during insert with STR_TO_DATE() function.

 

Example

INSERT INTO mytable (mydate) VALUES  (str_to_date('20012017', '%d%m%Y') );

[Barny74]

Hi. Is it better to keep the db as YYYYMMDD and echo out as DDMMYY on my website ?.

[thara]

It doesn't matter. You can retrieve dates in whatever format you want

 

Eg: 

SELECT DATE_FORMAT('2017-01-23', '%d/%m/%Y')

But, you must however save all dates using the YYYY-MM-DD format as that is the only format that mysql date fields can accept

(it is also the only format where you can sort into date order)..

Edited January 23, 2017 by thara

[Barny74]

Thanks Thara. I have it in the DB as YYYYMMDD , currently i echo it as

 

echo $row ['date'];

 

This is the part that is confusing me. How do i echo it out as DDMMYY

 

Thanks

[Barand]

This is the part that is confusing me. How do i echo it out as DDMMYY

 

 

 

You could try reading thara's post again and use the function he gave you.

 

edit - alternatively you can do it in PHP

$dtobj = new DateTime($row['date']);
echo $dtobj->format('d/m/Y');               //--> 23/01/2017, for example

Edited January 23, 2017 by Barand

[Barny74]

Thanks , i just get a bit confused if i am running sql query or pho code. Thanks and will give it a go

[Barny74]

Works perfectly thanks