CODEWITHSUNDEEP
sqlsql-server
Prashant Khadatkar
Prashant Khadatkar

Reputation: 200

Converting date in DD/MM/YY formate and order by it

I have table having two column 1) ProductId(int) 2) PublishDate(varchar) data is like following

ProductId   PublishDate
73          22/01/97
56          17/09/90
56          01/09/90
69          15/05/13
69          09/05/13

I have to get record from this table but in PublishDate Order by Desc, as PublishDate is varchar, I am not able to do this can anyone please help me for this.

I have tried following query:

SELECT T.ProductId,
       T.MYDATE
FROM
(
SELECT     ProductId, CONVERT(varchar(max), PublishDate , 101) AS MYDATE
FROM         DateValidation

) T
ORDER BY T.MYDATE DESC

Upvotes: 0

Views: 1696

Answers (3)

Anurag
Anurag

Reputation: 137

Try This 

SELECT ProductId,CONVERT(date,PublishDate,3)
FROM DateValidation 
order by 2 desc

Upvotes: 0

Romesh
Romesh

Reputation: 2274

Here is the SQLFIddel Demo

below is the Query Which You can try

Select ProductId,
       Convert(Date,substring(PublishDate,4,3) + 
                    substring(PublishDate,1,3) + 
                    substring(PublishDate,7,2))               
  From DateValidation
 Order By 2 Desc

Upvotes: 0

dani herrera
dani herrera

Reputation: 51665

Yo don't need to change date format because it is stored in your needed format ( it is stored as varchar, this is a dirty pattern).

But your issue is sorting by date. To sort by calendar date you need to convert varchar to a valid date:

SELECT     ProductId, PublishDate
  FROM     DateValidation
 ORDER BY  CONVERT(DATE, PublishDate, 103) desc

I encourage to you to store dates in a date format but varchar.

Upvotes: 3

Related Questions