Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ADO database, order date field (which is text)

Posted on 2006-03-20
20
Medium Priority
?
266 Views
Last Modified: 2010-04-05
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 ?
0
Comment
Question by:wildzero
  • 9
  • 5
  • 4
  • +2
20 Comments
 
LVL 9

Expert Comment

by:bernani
ID: 16241326
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
 
LVL 9

Expert Comment

by:bernani
ID: 16241394
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
 
LVL 9

Expert Comment

by:bernani
ID: 16241409
sorry:

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


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Author Comment

by:wildzero
ID: 16241427
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
 
LVL 9

Expert Comment

by:bernani
ID: 16242320

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
 
LVL 10

Author Comment

by:wildzero
ID: 16242333
And if the date formatting is in the other way
ie
mm/dd/yyyy

would that still work?
0
 
LVL 9

Expert Comment

by:bernani
ID: 16242704

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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 16244964
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
 
LVL 11

Expert Comment

by:pcsentinel
ID: 16246482
agree with mikelittlewood
0
 
LVL 10

Author Comment

by:wildzero
ID: 16250504
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
 
LVL 10

Author Comment

by:wildzero
ID: 16251913
When I just tried your code, I got an error - undefined fucntion CONVERT
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 16255650
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
 
LVL 10

Author Comment

by:wildzero
ID: 16261632
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 16267501
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 16267502
Sorry maybe I was not clear the first time round so I apologise
0
 
LVL 10

Author Comment

by:wildzero
ID: 16267518
kk let me try that
0
 
LVL 10

Author Comment

by:wildzero
ID: 16274580
It looks good, and sounds good, but I get the error
EOleException - Undefined functoin 'CONVERT' in expression

:/
Points upped
0
 
LVL 10

Author Comment

by:wildzero
ID: 16299333
:)
0
 
LVL 3

Accepted Solution

by:
dygj earned 1300 total points
ID: 16300453
if your database is an access try tcdate function:

select * from mytable order by cdate(mydatefield)
0
 
LVL 10

Author Comment

by:wildzero
ID: 16305761
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question