ADO database, order date field (which is text)

Hi there,

I have a date field in my database but it's set to text (due to a number of reseaons) and it works perfect for everything I do - except, when I try and order the date field it doesn't work as excepted

like I get

2/03/2006
21/03/2006
24/03/2006
5/03/2006

Is there a way I can sort the date field as date
ie
ORDER BY (date) AS Date ?
LVL 10
wildzeroAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bernaniCommented:
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).

0
bernaniCommented:
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 ?
0
bernaniCommented:
sorry:

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


0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

wildzeroAuthor Commented:
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?
0
bernaniCommented:

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 ....



0
wildzeroAuthor Commented:
And if the date formatting is in the other way
ie
mm/dd/yyyy

would that still work?
0
bernaniCommented:

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.

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

ORDER BY CONVERT(DateTime, YourStringDate, 103)
0
pcsentinelCommented:
agree with mikelittlewood
0
wildzeroAuthor Commented:
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?
0
wildzeroAuthor Commented:
When I just tried your code, I got an error - undefined fucntion CONVERT
0
mikelittlewoodCommented:
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?
0
wildzeroAuthor Commented:
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
0
mikelittlewoodCommented:
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)
0
mikelittlewoodCommented:
Sorry maybe I was not clear the first time round so I apologise
0
wildzeroAuthor Commented:
kk let me try that
0
wildzeroAuthor Commented:
It looks good, and sounds good, but I get the error
EOleException - Undefined functoin 'CONVERT' in expression

:/
Points upped
0
wildzeroAuthor Commented:
:)
0
dygjCommented:
if your database is an access try tcdate function:

select * from mytable order by cdate(mydatefield)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wildzeroAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.