Hello All and thanks in advance.

I am pulling availability records from a MySQL database which I want to ORDER BY the date field to list them in order.

Heres what I have tried:
Field type: Date
Problem: Forces into US format but I need it in UK

I tried to use
CONVERT
to convert the date from US format but it returns a Syntax Error, the full line is shown below:

sql = "SELECT * FROM Availability ORDER BY CONVERT(av_Date, '08,09,07', 2)"

Field type: Varchar
Problem: When sorted the records it sorts by the day value and ignores the month and year components.

I would greatly appriciate any help anyone can give me on this.

Kind regards

Me :)

Blitz wrote:

Heres what I have tried:
Field type: Date
Problem: Forces into US format but I need it in UK

NOT TRUE! In MySQL, *all* DATE and DATETIME fields are stored in YYYY-MM-DD hh:mm:ss format *internally*. True, if you use a database tool to execute queries, you may find that the *TOOL* (not MySQL!) converts that internal format to USA format. JUST IGNORE THAT!

In ASP code, when you retrieve a date or datetime value from MySQL, it is converted into a VBScript date type (actually VT_DATE in the underlying COM implementation).

So all you need to do is *DISPLAY* that in UK format. And you can do that easily by setting the Session.LCID to UK!

Whatever you do, do *NOT* store dates and times in VARCHAR fields. You *WILL* come to grief.

So...

Code:

<%
Session.LCID = 2057 ' use UK standard settings
...
sql = "SELECT * FROM Availability ORDER BY av_Date"
Set RS = yourOpenConnection.Execute( sql )
Do Until RS.EOF
    Response.Write RS("av_Date") & "<br>" & vbNewLine
    RS.MoveNext
Loop
RS.close
%>

Here's something I wrote many years ago. It discusses Access database, but aside from the internal format used everything it says applies to MySQL:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=189

By the by, the one problem with using MySQL with ASP is that all *LITERAL* dates and times you use with it must also be in that 'yyyy/mm/dd hh:mm:ss' format (yes, in apostrophes).

So I simply create a general purpose function to convert dates and times to that format for use in my SQL queries:

Code:

Function ISODate(dt)
    Dim fdt
    fdt = Year(dt) & "/" & Month(dt) & "/" & Day(dt)
    If DateValue(dt) <> dt Then fdt = fdt & " " & hour(dt) & ":" & minute(dt) & ":" & second(dt)
    ISODate = "'" & fdt & "'"
End Function

So then you can create SQL queries such as:

Code:

' get all records within last week:
SQL = "SELECT * FROM Availability WHERE av_date > " & ISODate( DATE() - 7 )
...

Of course, that's just an example. You could also do that query all in MySQL without the need for a literal date:

Code:

' get all records within last week:
SQL = "SELECT * FROM Availability WHERE av_date > DATE_SUB( CURDATE(), INTERVAL 1 WEEK ) "
...