Date order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date order by

Author

Topic

Nagesh1954
Starting Member

3 Posts

Posted - 2009-03-30 : 18:06:36

Hi
I have the followig data and my requirement is as under
please suggest me proper query to get the data as required.

"The list must be ordered by date of last contact, with the most recent contact first.The Last Contact column is stored as datetime.
The date should be displayed as MM/DD/YYYY"

------------------------------------
EMAIL ADDRESS LAST CONTACT
------------------------------------
mary@contoso.com 01/24/2003
kate@contoso.com 01/01/2004
julie@contoso.com 15.10/2002
kevin@contoso.com 10/10/2001
stefan@contoso.com 21/09/2000
stefan@contoso.com 06/12/2005
marc@contoso.com 07/13/2004
------------------------------------

Thanks
Nagesh

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-30 : 18:10:02

select EMAIL_ADDRESS,convert(varchar,LAST_CONTACT,101)
from <urtable>
order by LAST_CONTACT desc

Nagesh1954
Starting Member

3 Posts

Posted - 2009-03-31 : 12:09:11

Sorry Vijay, it did not work

I have changed your query as under and ran

select emailaddress,convert(varchar,LCdate,101) as Last_Contact
from dbo.TK_test
order by LCdate desc

This is how I got the output

stefan@contoso.com 21/09/2000
julie@contoso.com 15.10/2002
kevin@contoso.com 10/10/2001
marc@contoso.com 07/13/2004
stefan@contoso.com 06/12/2005
mary@contoso.com 01/24/2003
kate@contoso.com 01/01/2004

Nagesh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 12:42:21

Display problems should be handled on the front end (Crystal reports, Web page, etc).

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

bmatthews
Starting Member

8 Posts

Posted - 2009-03-31 : 16:44:49

try changing 'DESC' in your order by to 'ASC' and see if that works for you.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-31 : 16:50:45

You are typing the query wrong if what you explained is accurate.

If LCDate is trully a datetime column then the following query will provide you what you have requested.


Select Email_Address,LCDate
from 
Table1 a
order by LCDate desc

Format the date on the front end.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources