Date Sorting Issue

[saqib389]

Hi There

I am trying to sort the date but getting problems.. is there any one who can help me please

Problem is:

This is the page
http://wardownconsulting.co.uk/htmls...edule_test.asp

fieldname datatype
courseDate text

The reason for choosing datatype text is that because thats the requirement that if user enter any thing in date field it will appear as it is....

Here you will see the column Start Date in each cateogry, I want to display the date in descending order, and i have used this query

sqlCourses="select * from Courses where courseStatus = 1 and catID="&rsCategories("catID")&" order by courseDate DESC"

Result of this query is that its descending only the days not years and months
Result:
14/01/08
10/12/07
01/02/08

But infact the right result should be like this

14/01/08
01/02/08
10/12/07

Can any one help me regarding this that what should be the query in this case

Many Thanks
Looking forward to hear from you guyz
Saqib Azhar

[Allan Murphy]

As I have limited knowledge of sql I will use a query as an example. In a query I would add another field to transform the course date from dd/mm/yyyy to yyyymmdd or dd/mm/yy to yymmdd.

As an example, we will use a course date of 31/07/08 in the new field of the query enter the following course_date_value:format(mid([coursedate],7,2),"00") & format(mid([coursedate],4,2),"00") & format(mid([coursedate],1,2),"00") this will give you 080731,then in the sort line of this field select descending. You will need to change format(mid([coursedate],7,2),"00") to format(mid([coursedate],7,4),"0000") if your year is yyyy and not yy.

The format function ensures that there are two digits for the year, month and day

[TSQL_Oz]

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]

You could try:

select convert(varchar(6), [coursedate],12)

or just use this in the order by clause

select <bunch of fields including coursedate>
from <your table>
order by convert(varchar(6), [coursedate],12)

[TSQL_Oz]

Sorry, just realised this was an MS Access problem. Unless you have a SQL backend ignore the last helpful tip I made.

For Access you could check out the DatePart function. http://www.filex.com/htmlhelp/traxhelpDatePart.html

So using order by in your select clause:

select <bunch of fields>
from <your table>
order by
DatePart("yyyy", [coursedate]) & DatePart("m", [coursedate]) & DatePart("d", [coursedate])