CODEWITHSUNDEEP
sqldateselectmariadbdate-formatting
Normajean
Normajean

Reputation: 1275

How to retrieve data from MariaDB where any dates are in the format YYYY-MM-DD?

I'm retrieving data from MariaDB using:

SELECT * FROM table_name

Three columns in this data contain dates (and are formatted as dates in the form YYYY-MM-DD). When I receive them on the client side, they appear as "2021-07-11T14:00:00.000Z" but I instead want "2021-07-11". I've tried lots of things, including:

SELECT * FROM table_name DATE_FORMAT(date_column_one,'dd/mm/yyyy')

which doesn't work, as well as

SELECT *, DATE_FORMAT(date_column_one,'dd/mm/yyyy') FROM table_name

but this simply adds another column of data - I still get the other dates in the wrong format.

There is a lot of info about this stuff online but I can't find anywhere where they actually combine the formatting of dates with a basic select statement.

Upvotes: 0

Views: 2031

Answers (3)

Jason Ronne
Jason Ronne

Reputation: 1

It seems to use the STRFTIME abbreviations you can find them all here

https://www.programiz.com/python-programming/datetime/strftime

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you just want to return a date, then you can use the date function:

SELECT . . .,  -- the other columns
       DATE(date_column_one)
FROM table_name;

However, this returns a column with the type of date and you are at the mercy of your application to display it. Some applications might do you the "favor" of deciding that you want to see the time and timezone.

You can control this by converting the value to a string using DATE_FORMAT():

SELECT . . .,  -- the other columns
       DATE_FORMAT(date_column_one, '%d/%m/%Y')
FROM table_name;

Now the value is a string and its format will not be changed. The format '%Y-%m-%d' is the standard YYYY-MM-DD format, and I much prefer that.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

Your call to DATE_FORMAT is not using the format mask you seem to want here, which is yyyy-mm-dd. Try using the correct mask, and also don't select the date columns via SELECT *:

SELECT col1, col2, col3, ...  -- excluding date_column_one etc. 
       DATE_FORMAT(date_column_one, 'yyyy-mm-dd') AS date_column_one
FROM table_name;

Upvotes: 1

Related Questions