jet SQL 'order by date'

[tufanocak]

i have this code on my ASP page.it works,but i want
tell the SQL that ASIPTAR is a date(for example 23.04.2002).
On ASP page it makes a order like this:
03.09.2002 1 Onaylanmýþ
03.09.2002 2
03.10.2002 1
04.09.2002 1
04.10.2002 1 Onaylanmýþ
14.10.2002 1 Onaylanmýþ
29.08.2002 2 Onaylanmýþ
29.08.2002
This order is false,when ASIPTAR is a date.But when its a number
its not a problem.
So how can i tell SQL that 03.09.2002 ; 29.08.2002 are date not numbers.

sipliste.Source = "SELECT * FROM HASTA.KULARAPF WHERE AKULKD = '" + Replace(sipliste__MMColParam, "'", "''") + "' AND AKULHS='" + Replace(sipliste__kmkulhs, "'", "''") + "' ORDER BY ASIPTAR ASC, ASIPNO ASC"

[raf]

I'm not sure i understood everything.

You don't have to tell sql that it is date. 'SQL' will just create a recorset with a date as one of the variables.

The variable will be defined in your database as a variable from type date/time + a specified format.

I don't get what you mean with:

This order is false,when ASIPTAR is a date.But when its a number
its not a problem.


To display it, you can use some functions (depending on your scripting language + the format you want.)
To check whetter it's a date, you can use the IsDate() function (VB script) which return a 'true' or 'false' value.
etc

For more info about these function, search for the file "mse.exe " on your PC, and check the helpfunction.

If this doesn't help, please clarify your post and tell us what exactly is the problem.

[whammy]

Actually it looks like it's being stored as a number and not as a date/time field... which may be the problem. What is the datatype for the field itself in your database?

[tufanocak]

i use the AS/400 database and the ASIPTAR field
are char(10) character fileld.Can we read a character field as
a date field with a conversion or somethink else with Jet SQL.

[glenngv]

try:

ORDER BY CAST(ASIPTAR as datetime) ASC

not too sure if casting string to date will also work in ORDER BY clause as in the case of WHERE clause where it is normally used.

[whammy]

Good one, Glenn. You just taught me (and someone else I know) something about SQL (as I've never had to use CAST). :D

But I messed around with it and see exactly what it does with datatypes, and how it may be useful sometime in the future... :D

[glenngv]

thanks whammy. :)

Other uses of CAST:

SELECT * FROM TableName WHERE CAST(strDate as datetime) BETWEEN #10/01/2002# AND #10/16/2002#

[whammy]

;)

Ahh how elegant some things can be (although I use BETWEEN now... I used to use a SQL datetime function to do it but it seems so bloated!)... it seems to me like cast might especially be useful if you're working with someone ELSE's database where they didn't plan well? :D