Order By Date/Time Issue

[Trevor at IU]

While this is more an annoyance than a problem, I'm wondering if it's something
I'm doing or systematic.

I'm running the query below to pull meetings based on a selected date from a
previous form and organize those meetings by start time.

<cfquery name="openmeetings" datasource="Scheduling">
SELECT *
FROM meetings
WHERE program_code = '2006SU'
AND event_code = '#form.date#'
AND meeting_type = 'FACM'
AND num_attendees < max_attendees
ORDER BY start_time
</cfquery>

I get the proper results, but they're not sorted by start time. After trying
a million different DatePart, LSTimeFormat, and TimeFormat functions in the
'order by' statement (none of which worked), I finally tried just running a
query of queries shown below to test if that would work.

<cfquery name="haha" dbtype="query">
SELECT *
FROM openmeetings
ORDER BY start_time
</cfquery>

This produced all the correct results AND ORDERED THEM PROPERLY BY TIME. I
can't figure out why. The database I'm pulling from is MS Access, the
start_time field is set to date/time format, although many of the entries are
"2:00 pm" or "11:00 am" instead of a full defined date and time. The query
result for the start_time field is always something like1899-12-30 11:10:00.0

Like I said, it's only a minor inconvenience to run the extra query to sort
the results, but can anyone explain why the original query won't?? Any help is
appreciated.

Trevor

[BKBK]

Did you use the function CreateODBCDateTime() when populating the start_time
column? The value "1899-12-30 11:10:00.0" already gives an indication why there
might be problems.

For most operating systems and databases, and I would presume, for MSAccess,
too, the zero value relative to which dates are measured is '1970-01-01
00:00:00' UTC. Sorting by date may not be possible if the start_time column
has dates before that value. I am therefore guessing that the reason the
sorting by query of query works because it is done by Coldfusion, which allows
datetimes before 1970. Just an idea.

[Trevor at IU]

What you said seems to be consistent with what I'm seeing. As the data source
is littered with inaccurate dates, it looks like running a second query is the
easiest manner of fixing this. I'm glad to know that it was nothing my sytax
of anything like that. Thanks for the input!!

[JMGibson3]

What's your database? Most (all?) DB's have functions to suck out only the
time or date. For example in Access:
ORDER BY Format(StartTime,'HH:MM:SS')
then you could skip the overhead of an extra query.

[Avinash]

Simple solution...
SELECT * FROM table_name ORDER BY dt desc, ORDER BY TM DESC;

dt is date type,
tm is time type.