CODEWITHSUNDEEP
mysqldate
VORAS9319
VORAS9319

Reputation: 13

SQL Change date format from yyyy-mm-dd to dd-mm-yyyy

I have created MySQL table :

CREATE TABLE EMP(
 EMPID INTEGER NOT NULL (5),
 SURNAME VARCHAR(25),
 SAL INTEGER(5),
 JON VARCHAR(25),
 START_DATE DATE,
 END_DATE DATE,
 DEPNO INTEGER(5)
 );

with following records:

INSERT INTO EMP
 (EMPID,SURNAME,SALARY,JOB,START_DATE,END_DATE,DEPNO)
 VALUES 
 ('1','Vorosila','500000','COO','20150101',null,'1');

however I need to change date format from 2015 01 01 to 01 01 2015

Can anybody show me or tell me how to do that ?

THANK YOU IN ADVANCE

Upvotes: 0

Views: 2010

Answers (4)

wallyk
wallyk

Reputation: 57774

You can do what you probably want by creating a view and referring to that instead of the (underlying) table.

CREATE VIEW emp_view AS
  SELECT empid,
     surname,
     sal,
     jon,
     date_format(start_date, '%d-%m-%Y') as start_date,
     date_format(end_date, '%d-%m-%Y') as end_date,
     depno
   FROM emp;

Note that this changes the type of the date columns to varchar, so comparisons will no longer work as expected:

SELECT * FROM emp_view WHERE start_date > '01-12-1924';   // fails!

Upvotes: 0

mrmryb
mrmryb

Reputation: 1509

You cannot change the default date format in mysql.

I once hoped for the default date to be editable so I wouldn't have to jump through these hoops to get the date I actually wanted, mysql even has a date format system variable, but it is unused. Date Format Mysql - link

What you should really do is store it as the default format Year-Month-Date and then convert it on select.

The first thing I'd suggest is having your date columns as date types, which would give your dates the following format '2015-01-01'.

If you do this then you can use DATE_FORMAT - link - the second value in the DATE_FORMAT function allows you to customise the returned date, and there are many different thing you can do with this if you look at the link:

SELECT 
DATE_FORMAT(`START_DATE`,'%d-%m-%Y') 
AS `START_DATE` 
FROM ...

The other option you have is to store your dates in the format that you already want as a char or varchar column.

HOWEVER, as should be obvious, this column will not be treated as storing dates, and so will not give you the correct comparisons in a where clause when using > < BETWEEN or the correct ordering in an order by clause. It is after all just a string of numbers in this case.

However you can then use STR_TO_DATE - link if you did need to use a where or order by on this column to change it back to a date within the query - in this case the second value is the custom format of your 'dates' in the column. Keep in mind with a where you will need to compare it with the correct mysql format as shown below:

SELECT
`START_DATE`
FROM table
WHERE STR_TO_DATE(`START_DATE`,'%d-%m-%Y') BETWEEN '2015-01-01' and '2016-01-01'

Upvotes: 1

Jim Garrison
Jim Garrison

Reputation: 86774

DATE values do not have a "format", they are objects that represent instants in time (or entire days, but still independent of formatting).

Formats are applied on input and output, so you just need to apply the correct format, which you can find in the MySQL manual, to the SELECT statement.

Upvotes: 1

Jean-Fran&#231;ois Savard
Jean-Fran&#231;ois Savard

Reputation: 21004

In MySQL you can change the format of a date using DATE_FORMAT method which is similar to to_char in Oracle.

DATE_FORMAT(SYSDATE(), '%DD-%MM-%YYYY');

For more information about specifiers check this thread http://www.sqlines.com/oracle-to-mysql/to_char_datetime

Upvotes: 0

Related Questions