Click here to Skip to main content
15,610,690 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 dataype 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 28-08-2004

please help me

Cast the nvarchar value to datetime and sort it...
order by CONVERT(datetime, DOJInGovrService , 102) asc

102 is the code for the datetime format - see options here:[^]
Share this answer
Member 11589183 12-May-15 6:21am    
It is giving error
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
Kornfeld Eliyahu Peter 12-May-15 6:34am    
102 is probably not the right format number for you - check the table at the link provided...
If your DOJInGovrService is a DATE or DATETIME column, that that will work.
But...your comment "Date entered is in format 28-08-2004" implies that it's stored as a string. If so, the best solution is for you to fix your DB first, and replace all columns which are stored as strings with the most appropriate data type: DATE, DATETIME, INT, and so on - continuing the way you are will only lead to massive complication in your SQL queries.
Strings are sorted on a character-by-character basis, so the first different character decides the order. You don't need that, because that is useless for dates. Unfortunately, while it is possible to convert the string date to a DATE in the query (but not necessarily reliable, depending on how many coders and / or users have inserted or updated the DB) it's a problem that will give you hassle from now until you leave the company.

Change your DB now, while the code base is small, and the data is also small! If you wait, the problem will become a major change. A small change now, and your current problem will go away, and a whole lot of future problems as well.
Share this answer
Member 11589183 12-May-15 6:43am    
thanks for your reply. I think 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.
OriginalGriff 12-May-15 6:55am    
Backup your DB.
Create a new column.
Convert all the old values to the new column.
Delete the old column.
Modify your software to use the new column only.

The major problem that storing dates as strings is going to give you: at some point 9if it hasn't already) someone is going to put "bad data" in there. and once that happens, your app will fail every time you try to do =anything useful with the "date" data.
If you replace the column with DATETIME now, you catch every one of the existing problem rows (if any), and can fix them. And they can't be a problem again!
Member 11589183 12-May-15 7:03am    
thanks i will accept your answer because it will help for others to remove the problem from root. But also 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"
Here is my example, hope this help you a bit.
My table is:
Id(int)    SortFields(varchar(50))
1	   28-08-2004
2	   29-08-2004
3	   01-01-2001
4	   15-11-2000

SQL query:
(SELECT [Simply].Id, CONVERT(datetime, [Simply].SortFields, 105) AS FDate
  FROM [dbo].[Simply]) AS tb

And result:
Id	FDate
4	2000-11-15 00:00:00.000
3	2001-01-01 00:00:00.000
1	2004-08-28 00:00:00.000
2	2004-08-29 00:00:00.000
Share this answer
Member 11589183 12-May-15 6:26am    
thanks for reply it is giving following error
"Conversion failed when converting date and/or time from character string"
anpxbkhn 12-May-15 6:36am    
This error cause values in your field are not correct format. Are you sure all of values have format 28-08-2004 (dd-mm-yyyy). Check here and replace 105 by correct style

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900