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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.