CODEWITHSUNDEEP
phpmysqldatesql-order-by
rangaraj seo
rangaraj seo

Reputation: 31

mysql value sort by date but this column type is not a date type

i have a database values values like this format

premium_paid_date
31-10-17
30-10-17
11-10-18
31-08-18
31-10-17
25-11-17

but it was stored type is var-char. my customer wants this table ORDER BY year. which mean based on last two digits and followed by month and date. how can i order this using MySQL without convert into date format

Upvotes: 0

Views: 37

Answers (3)

rangaraj seo
rangaraj seo

Reputation: 31

Finally i solved this issue my answer is

SELECT * FROM `tablename` order by STR_TO_DATE(premium_paid_date, '%d-%m-%Y') desc

Upvotes: 1

AFM-Horizon
AFM-Horizon

Reputation: 129

Something like:

SELECT [premium_paid_date] FROM [dbo].[premium_paid_date] ORDER BY SUBSTRING(Date,5,6)

then you are only ordering it by the last 2 digits of the date (the year) using the substring.

Upvotes: 1

han
han

Reputation: 118

You can convert string to date in your order by statement. Like

SELECT Premium_paid_date FROM Table_Name ORDER BY Convert( DateTime, premium_paid_date, 102) DESC

see date format

Upvotes: 1

Related Questions