how to sort by Date field in ascending order

Question

0.00/5 (No votes)

See more:

Hello,
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.

SQL

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

Posted 11-May-15 22:25pm

Member 11589183

Add a Solution

3 solutions

Solution 1

Cast the nvarchar value to datetime and sort it...

SQL

order by CONVERT(datetime, DOJInGovrService , 102) asc

102 is the code for the datetime format - see options here: https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

Posted 11-May-15 22:48pm

Kornfeld Eliyahu Peter

Comments

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...

Solution 3

Here is my example, hope this help you a bit.
My table is:

SQL

Id(int)    SortFields(varchar(50))
1	   28-08-2004
2	   29-08-2004
3	   01-01-2001
4	   15-11-2000

SQL query:

SQL

SELECT *
FROM
(SELECT [Simply].Id, CONVERT(datetime, [Simply].SortFields, 105) AS FDate
  FROM [dbo].[Simply]) AS tb
ORDER BY tb.FDate ASC

And result:

SQL

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

Posted 11-May-15 23:02pm

anpxbkhn

Comments

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 https://msdn.microsoft.com/en-us/library/ms187928.aspx and replace 105 by correct style

Add a Solution

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

OriginalGriff · Accepted Answer · 2015-05-11T22:54:00

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.

how to sort by Date field in ascending order

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

how to sort by Date field in ascending order

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us