ADO database, order date field (which is text)

Hi

Have you try to add a 0 before the days in the range 1 to 9 ? In this way, your numbers are sorted in the good order (adding a leading zero).

Tested in a listbox pasting the data you supplied: with sorted set to true: with a leading zero the dates are correctly sorted but no with the initial 0 before 1 and 5.

I presume it's the same for your database ?

sorry:

 read ... but no without the initial 0 before 1 and 5.

When I insert the date into the data base
ie
  adoDB.FieldByName('date').asString          := DateToStr(dtp1.date);

what would be the way to do that then?

var
Str : String;
begin
str:= '1/03/2006';
Str := StringOfChar('0', 10 - Length(Str)) + Str;
// Str returns 01/03/2006 - the date + separators is 9 chars, 0 is added
str:= '12/03/2006';
// Str returns 12/03/2006 - the date + separators is already 10 chars, no 0
end;

In your code, it becomes sth like:

adoDB.FieldByName('date').asString:= StringOfChar('0', 10 - Length(DateToStr(dtp1.date))) + DateToStr(dtp1.date);  

I presume it should work ....

And if the date formatting is in the other way
ie
mm/dd/yyyy

would that still work?

You're right. Better, with FormatDateTime

d = 1 / dd = 01
m = 3 // mm = 03

var
S, T: String ;
begin
S := FormatDateTime('dd/mm/yyyy', StrToDateTime('1/2/2006'));
T := FormatDateTime('mm/dd/yyyy', StrToDateTime('1/2/2006'));

S = 01/02/2006
T = 02/01/2006

in your code, should be sth like:

adoDB.FieldByName('date').asString := FormatDateTime('dd/mm/yyyy', StrToDateTime(DateToStr(dtp1.date)));
or
adoDB.FieldByName('date').asString := FormatDateTime('mm/dd/yyyy', StrToDateTime(DateToStr(dtp1.date)));

Test and see if it works.

Could you not just convert the string to a date in the order by clause of your query?

ORDER BY CONVERT(DateTime, YourStringDate, 103)

agree with mikelittlewood

yea thats how I thought you could do it
want to explain that some more

DateTime is like String or Boolean I presume
YourStringDate - would that be the column?
and whats 103?

When I just tried your code, I got an error - undefined fucntion CONVERT

Ok I assumed you are using an SQLServer database as this is a standard function.

the function works like this:
Parameter 1, data type to convert to
Parameter 2, data to convert from string
Parameter 3, date time format (in your case dd/mm/yyyy)

CONVERT(DateTime, '2/03/2006', 103)

What database are you using?

Using an ADO database calling the db via AdoQuery
The problem is the column is in VarChar (string) and I am trying to sort it but it's not sorting the Dates correctly...
So how will CONVERT(DateTime, '2/03/2006', 103) help?
Wouldn't it be better
Data type, Column Name, Formatting

Because CONVERT(DateTime, '2/03/2006', 103) will change everyone of the string dates to proper datetime and THEN order it afterwards.

You can also then choose to do it up or down

ORDER BY CONVERT(DateTime, FIELDNAME, 103)
or
ORDER BY CONVERT(DateTime, FIELDNAME, 103) DESC

Notice you dont actually put in an explicit string into the fieldname part, put the name of the column holding the string dates
ie

MYDATECOLUMN
'2/03/2006'
'1/03/2006'
'10/03/2006'
'3/03/2006'

ORDER BY CONVERT(DateTime, MYDATECOLUMN, 103)

Sorry maybe I was not clear the first time round so I apologise

kk let me try that

It looks good, and sounds good, but I get the error
EOleException - Undefined functoin 'CONVERT' in expression

:/
Points upped

:)

Woah... I was quite skeptical about the cdate (as I did a quick search on Google and found nothing for it) however I tried it and it worked perfectly!