[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I sort dates in descending order using a SQL Statement ?

Posted on 2009-02-20
5
Medium Priority
?
1,238 Views
Last Modified: 2012-08-14
I am developing an Access application using Access as the front end and SQL Server as the back end database. I want to populate a Listbox with the RESULT SET of a SQL statement to display dates in DESC order from a table.

I run the following SQL Statement but I am not getting the results I would like.
Is there a way to rewrite this SQL Statement:
SELECT distinct convert(varchar,TodaysDate,101) As TD FROM dbo.tblDailyUDL order by TD DESC

I get results like the following:
12/31/2008
12/31/2007
12/30/2008

I would like to get results like the following:
12/31/2008
12/30/2008
12/31/2007

The field TodaysDate in the table tblDailyUDL is defined as "varchar 30". I am using this table which I did not create.

I will be passing the date selected from this Listbox to a Stored Procedure in the format
MM/DD/YYYY.
0
Comment
Question by:zimmer9
  • 2
  • 2
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23698068
SELECT distinct convert(varchar,TodaysDate,101) As TD FROM dbo.tblDailyUDL order by TodaysDate DESC
0
 

Author Comment

by:zimmer9
ID: 23698076
SELECT distinct convert(varchar,TodaysDate,101) As TD FROM dbo.tblDailyUDL order by TodaysDate DESC

I get the following complaint in SQL Query Analyzer:

Server: Msg 145, Level 15, State 1, Line 24
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 23698082
SELECT convert(varchar,TodaysDate,101) As TD FROM dbo.tblDailyUDL GROUP BY TodaysDate order by TodaysDate DESC
0
 

Author Comment

by:zimmer9
ID: 23706215
SELECT convert(varchar,TodaysDate,101) As TD FROM dbo.tblDailyUDL GROUP BY TodaysDate order by TodaysDate DESC

The above SQL statement yields the following results which are in descending order by MONTH, DAY, then YEAR:

12/31/2008
12/31/2007
12/30/2008

Is it possible to modify the SQL statement to produce the dates in descending order so they look as follows in descending chronological order by YEAR, then MONTH, then DAY:

12/31/2008
12/30/2008
12/31/2007
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23706272
try this
SELECT convert(varchar,convert(datetime,TodaysDate),101) AS TD 
  FROM dbo.tblDailyUDL GROUP BY TodaysDate order by convert(datetime,TodaysDate) DESC 

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month19 days, 16 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question