How to sort by varchar field containing a date in ascending order

Question

In design time we have given [DOJInGovrService] field as varchar datatype.
now when we are trying order by this parameter(DOJInGovrService) in ascending order it is not giving the desired result.
I know it is datatype problem but I can't change the datatype now as data is already entered.

SELECT ED.class, 
       ED.CurrentOfficePlace, 
       ED.DOB, 
       ED.DOJInCurrentOff, 
       ED.DOJInGovrService, 
       ED.DOJInSamvarg, 
       ED.EmpName, 
       ED.HomePlace, ED.Qualification 
FROM tbl_EmplyeesBiodata ED
ORDER BY DOJInGovrService asc

Date entered is in format dd-MM-yyyy (i.e. 28-08-2004).

please help me

How about adding an additional column to your table which has type `datetime2` (which will allow proper sorting by date), `UPDATE` the table to fill the new column based on the values from the existing `DOJInGovrService` column, (optionally delete the old column and change your code), and do a `ORDER BY NewDateTimeColumn`? — stakx - no longer contributing, May 12 '15 at 08:29
Of course you can change it. Create a new datetime-column(or date), then use an update statement which updates the new column with the date value, use `Convert` or `Cast` to convert it from varchar to datetime. Finally delete the old column and rename the new to the old. Change it, the earlier the better. — Tim Schmelter, May 12 '15 at 08:29
Do ***NOT*** store dates in a `varchar` column. Fix your data model and you will not have any problems doing the sorting properly — , May 12 '15 at 08:47
@a_horse_with_no_name That's true. What with situation when RDBMS doesn't provide format date that you need? — Robert, May 12 '15 at 08:51
Then I would immediately dismiss that RDBMS as a serious consideration to be used in any production environment. — , May 12 '15 at 08:55
thanks for your replies. Creating new column and update it with the old column data is the only solution. But Update the new column with the values in old column, data will remain the same though !!!! so again it will create the problem. — c.jack, May 12 '15 at 10:41
thanks this query worked for me thanks.. "select ED.class,ED.CurrentOfficePlace,ED.DOB,ED.DOJInCurrentOff,ED.DOJInGovrService,ED.‌DOJInSamvarg,ED.EmpName,ED.HomePlace,ED.Qualification from tbl_EmplyeesBiodata ED order by convert(date,DOJInGovrService,105) asc" — c.jack, May 12 '15 at 11:09

Robert · Answer 1 · 2015-05-12T11:01:53.863

2

Try to convert it to datetime

select ED.class,ED.CurrentOfficePlace,ED.DOB,ED.DOJInCurrentOff,ED.DOJInGovrService,ED.DOJInSamvarg,ED.EmpName,ED.HomePlace,ED.Qualification 
from tbl_EmplyeesBiodata ED 
order by convert(date,DOJInGovrService,105) asc

This is only direct solution. The best way to do that is create new column with date type. The column will helps you to create query without cast or convert.

edited May 12 '15 at 11:01

answered May 12 '15 at 08:28

Robert

25,425
8
67
81

This is certainly the answer to the question as asked, but do bear in mind that when some value that isn't castable to a datetime inevitably makes its way into this field, this will stop working, and it will be a pain to track down the problem. Also, this will be slower than using a real datetime field with an index on it. Pay heed to the comments and consider taking the extra time to change the field itself to a datetime value. – Matt Gibson May 12 '15 at 08:37
thanks for your suggestion parado. I also think creating new column is the only solution, as i have tried your query it is also giving error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value" – c.jack May 12 '15 at 10:33
@ankitsrist probably some of dates are not in correct format – Robert May 12 '15 at 10:36
This query worked for me thanks.."select ED.class,ED.CurrentOfficePlace,ED.DOB,ED.DOJInCurrentOff,ED.DOJInGovrService,ED.DOJInSamvarg,ED.EmpName,ED.HomePlace,ED.Qualification from tbl_EmplyeesBiodata ED order by convert(date,DOJInGovrService,105) asc" – c.jack May 12 '15 at 10:58
@ankitsrist Thanks, for the solution, I updated my answer to make it correct. – Robert May 12 '15 at 11:01

score 2 · Accepted Answer · edited May 23 '17 at 11:58

This is just one of many reasons why you should Always use appropriate data types.
Even when you have data in the table, you can change the data type using an alter table ddl statement:

ALTER TABLE tableName
ALTER COLUMN ColumnName datetime2;

However, you should copy this table first and try the alter on the copy, so that if it messes up your data you will not risk anything.
If the alter is successful then do it on your live table. if not, you can go with a different approach, involving these stages:

rename the DOJInGovrService column to DOJInGovrService_old. use sp_RENAME.
Add a column DOJInGovrService with the correct datatype (datetime2), using alter table ddl stement.
Update the new DOJInGovrService column with the values in DOJInGovrService_old. you will probably need to use convert.
drop the DOJInGovrService_old column using the alter table ddl statement.

thank you so much Zohar Peled. I think this is the only solution :) — c.jack, May 12 '15 at 10:37
Glad to help. However, If you find an answer that provide the correct solution to your problem you should accept it so that other people will know that the problem is solved. — Zohar Peled, May 12 '15 at 10:38

How to sort by varchar field containing a date in ascending order

2 Answers2