Convert the mysql date format to dd-mmm-yyyy in mysql

Question

I have maintained a table with start_date as "date" datatype and now I wanted to display the date in dd-mmm-yyyy format.

public static BusObjectIterator<com.timesheetmd.weeksView> weeksOfYear(int year) { String queryText = "SELECT `id`, `year`, CONCAT(CAST(`start_date` AS CHAR), ' to ', CAST(`end_date` AS CHAR)) AS 'week' FROM `weeks` WHERE `weeks`.`year` = :year AND end_date <= CURRENT_DATE ORDER BY start_date DESC"; QueryObject query = new QueryObject(queryText); query.addParameter("year", "weeks.year ", QueryObject.PARAM_INT,year);//NOPMD query.setResultClass(weeksView.class); return query.getObjects(); }

As I want to return the result in the form of BusObjectIterator. Is it possible to change the format of the date in the query itself?

O. Jones · Accepted Answer · 2021-01-27 11:38:51Z

6

You need DATE_FORMAT(start_date,'%d-%b-%Y') instead of your CAST(...AS CHAR).

Here's the DATE_FORMAT() writeup.

edited Jan 27, 2021 at 11:38

answered Sep 30, 2014 at 12:26

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chethan Over a year ago

If I use DATE_FORMAT. Will it affect the return type?

VMai Over a year ago

You've had a string by your concatenation, it will remain a string. You don't need to cast it anymore though.

Collectives™ on Stack Overflow

Convert the mysql date format to dd-mmm-yyyy in mysql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related