Sort on Date and Time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Two of the fields in my table, v-table are Date/Time fields. One called v-date has format Long Date and the other called v-time has format Short Time.

Consider this query :-

select * from v-table order by v-date DESC, v-time DESC;

Seems OK but I get these results :-

Other fields 22 January 2004 08:00
Other fields 21 January 2004 09:15
Other fields 20 January 2004 11:52
Other fields 20 January 2004 10:30
Other fields 20 January 2004 10:45

Why are the 10:30 & 10:45 rows in the wrong order? How do I fix?

Thanks.
 
Andy

If both are Date/Time fields, both store a "point in time". It is
irrelevant that you've formatted the display of the first to show just the
date portion of its contents, and the second to display only the time
portion.

My guess is that you will find that the date portion of your [v-time] field
is different for those last two.

Try this -- display both as General date format and examine their actual
contents.

Consider this -- why have two fields to store date and time when it appears
you want to store the time on a specific date? You can do this in a single
date/time field, display the portions as needed.
 
Thanks Jeff - well done - It was to do with point in time.

I had orginally stored the 3 records for the 20th January using a default value of Now() for both v-date and v-time.
I had then updated the record with the time of 10:45 from date and time values in a form.
The v-time DESC sort was then comparing 2 records as below :-
20/01/04 10:30:32
10:45:00

The 10:45 will come below 10:30 despite being later in time.

I have now solved the problem by changing the original default values for v-date and v-time to Date() and Time() respectively.
 
Back
Top