How to correctly Sort date in GridView

Question

1.00/5 (1 vote)

See more:

While performing sorting in GridView this is the result:

13-Dec
19-Dec
20-Dec
14-Dec
13-Dec
10-Dec
15-Dec
16-Dec

which is not correct, instead it should be:

10-Dec
13-Dec
13-Dec
14-Dec
15-Dec
16-Dec
19-Dec
20-Dec

please help to 'Sort' this out...Also it is a DATE type column...

Code was:

SQL

SELECT status,name, dob, category FROM Family_Details 
WHERE ((DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, dob, getdate()) + 1, dob))) % 366 <= 10)ORDER BY DESC

I also used:

SQL

SELECT status,name, dob, category FROM Family_Details 
WHERE ((DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, dob, getdate()) + 1, dob))) % 366 <= 10) ORDER BY CONVERT(DATETIME, DOB, 103) DESC

Posted 9-Dec-15 20:08pm

Member 12133159

Add a Solution

Comments

aarif moh shaikh 10-Dec-15 2:36am

order by DOB asc

Member 12133159 10-Dec-15 2:41am

Not working...

Jawad Ahmed Tanoli 10-Dec-15 6:49am

what is datatype of DOB in database ?

Member 12133159 10-Dec-15 7:03am

Sir its already there...Date type

Anisuzzaman Sumon 10-Dec-15 13:22pm

If it's type is date then simply

SELECT status,name, dob, category FROM Family_Details WHERE ((DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, dob, getdate()) + 1, dob))) % 366 <= 10)
order by dob asc

Member 12133159 11-Dec-15 0:26am

But its not working...
The result is Here in the question itself...

CHill60 11-Dec-15 16:47pm

In your question you are not using ORDER BY correctly in the first query you have shown us. In the 2nd query there is absolutely no need for the CONVERT on the date.
Also the results you have shown imply there is some other CAST or CONVERT happening as the year is missing from the DATEs

Member 12133159 12-Dec-15 4:46am

Can you tell me How to sort it by using (Day)Part of date column only, so that it can be sorted because this command is giving same result as mentioned above...
Also i have used {0:dd/MMM} to eliminate Year part of the date...

1 solution

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)

Member 12133159 · Accepted Answer · 2015-12-15T18:25:00

I got the correct answer:

SQL

SELECT dob FROM fd WHERE ((DATEDIFF(dd, getdate(), DATEADD(yyyy, 

DATEDIFF(yyyy, dob, getdate()) + 1, dob))) % 366 <= 10)
ORDER BY MONTH(dob)ASC ,DAY(dob) ASC

This Query will sort the Birthdays coming in next 10 days by Date...

Thanks everyone and Thanks to me too...:)