How to sort a database in respect to date

Question

0.00/5 (No votes)

See more:

SQL

Select Top 10 * from News_Events order by NewsDate;

I have tried the above query; but the output is
1/10/2011 12:00:00 AM
1/11/2011 12:00:00 AM
2/10/2011 12:00:00 AM
2/11/2011 12:00:00 AM
3/10/2011 12:00:00 AM
3/11/2011 12:00:00 AM

NewsDate data-type is smalldatetime.

Anyone please suggest me some way to solve this.

Thanks in advance.

Posted 9-Nov-11 19:40pm

sahabiswarup

Updated 9-Nov-11 20:15pm

RaisKazi

v2

Add a Solution

Comments

RaisKazi 10-Nov-11 2:16am

Probably it's in "mm/dd/yyyy" format, and if it is in "mm/dd/yyyy" format then result is correct.

sahabiswarup 10-Nov-11 4:26am

date format is dd/mm/yyyy

Amir Mahfoozi 10-Nov-11 10:26am

Could you please show the output of "Select *, CONVERT(varchar, newsdate), from News_Events order by NewsDate;"

3 solutions

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)

Mehdi Gholam · Answer 1 · 2011-11-09T22:11:00

Solution 1

Check your date column is datetime instead of a varchar and your sorting will be done correctly and faster than sorting strings.

Also the date view format could be mm/dd/yyyy and not dd/mm/yyyy like RaisKazi said.

Posted 9-Nov-11 22:11pm

Mehdi Gholam

Comments

P.Salini 10-Nov-11 4:18am

She has mentioned that datatype is smalldatetime

Mehdi Gholam 10-Nov-11 4:21am

No harm in rechecking :)

sahabiswarup 10-Nov-11 4:25am

P.Salini there is some mistake by u to identify me, i'm a guy.

sahabiswarup 10-Nov-11 4:21am

yes...i have already mentioned that NewsDate datatype is smalldatetime

Amir Mahfoozi · Answer 2 · 2011-11-09T22:45:00

If you are sure that the type of the field is smalldatetime so the output is surely correct so to convince yourself that its correct try running this SQL which will show you the month names and you will understand that the problem is, probably, in the way you are looking to those numbers :

SQL

Select  * , CONVERT(varchar, newsdate),
             from News_Events order by NewsDate;

sachin10d · Answer 3 · 2011-11-09T23:12:00

Solution 3

Try this

SQL

Select Top 10 * from News_Events order by convert(datetime,NewsDate);

Posted 9-Nov-11 23:12pm

sachin10d

Comments

sahabiswarup 10-Nov-11 5:19am

still i get the same output:

2011-01-10 00:00:00
2011-01-11 00:00:00
2011-02-10 00:00:00
2011-02-11 00:00:00
2011-03-10 00:00:00
2011-03-11 00:00:00

Heino Zunzer 10-Nov-11 6:33am

yes, and what's your concern with that? That is the correct order.
the Format here is YYY-MM-DD

let me translate:
2011-01-10 = January 10, 2011
2011-02-10 = February 10, 2011
2011-03-10 = March 10, 2011

The format in your original post was MM/DD/YYYY (US format)

So you see, the order is correct.

sahabiswarup 10-Nov-11 7:19am

these above solution doesn't solve my problem.

Heino Zunzer 11-Nov-11 4:47am

It's not a solution, it's an explanation.
Because I don't know, what your problem is.
THE DATES ARE IN THE CORRECT ORDER!

1/10/2011 12:00:00 AM -> Jan. 10
1/11/2011 12:00:00 AM -> Jan. 11
2/10/2011 12:00:00 AM -> Feb. 10
2/11/2011 12:00:00 AM -> Feb. 11
3/10/2011 12:00:00 AM -> Mar. 10
3/11/2011 12:00:00 AM -> Mar. 11

How else would you want them sorted?
Do you want them displayed in another format? DD/MM/YYYY?

Please state what output you do expect. Then I can (maybe) tell you, how to get there. but sorting is not your problem.

How to sort a database in respect to date

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us