Order by date question

Hello Jeff!!

This is something, your SQL Server should manage. If you are using Microsoft SQL Server, you can do it that way:

-----------------
select      *
from      yourtable
order      by Abs(DateDiff(s, YOUR_DATE, DATE_COLUMN)) ASC
-----------------

for example

-----------------
select      *
from      yourtable
order      by Abs(DateDiff(s, '2004-25-05', [Timestamp])) ASC
-----------------

There is still a little problem, though: This selection is quite accurate, because the difference is measured in seconds (see the S in DateDiff function). But when you exceed the timespan of 68 Years, it will cause an error, an you will need to use MI for minutes :)


Eclipse2k

Its me again,
here is a real example i tested:

-----------------
select      TOP 25 [Timestamp]
from      logging
order      by Abs(DateDiff(s, '2004-27-07 12:30:00', [Timestamp])) ASC
-----------------

Results:

-----------------
2004-07-27 12:34:52.760
2004-07-27 12:34:53.167
2004-07-27 12:24:28.780
2004-07-27 12:24:28.860
2004-07-27 12:37:50.183
2004-07-27 12:37:50.230
2004-07-27 12:37:50.230
2004-07-27 12:45:37.847
2004-07-27 12:45:37.893
2004-07-27 12:45:37.910
2004-07-27 12:13:35.337
2004-07-27 12:13:35.400
2004-07-27 12:13:32.260
2004-07-27 12:13:32.320
2004-07-27 12:13:31.040
2004-07-27 12:13:30.993
2004-07-27 12:13:29.010
2004-07-27 12:13:28.947
2004-07-27 13:13:08.030
2004-07-27 13:13:08.093
2004-07-27 13:14:31.743
2004-07-27 13:14:31.773
2004-07-27 13:15:27.257
2004-07-27 13:15:27.287
2004-07-27 13:15:27.303
-----------------

Eclipse2k

Here is my query:  I am not sure how this works with this [TimeStamp] function.  I am using SQL 2000, but I am not a pro at SQL.

<CFQUERY name="getMeetings" datasource="#ds#">
SELECT     CMEProgramSym.*, CMEPrograms.CMEProgramName, CMEPrograms.CMEProgramSymposiumName,
                      Users.*
FROM         CMEProgramSym INNER JOIN
                      CMEPrograms ON CMEProgramSym.CMEProgramID = CMEPrograms.CMEProgramID INNER JOIN
                      Users ON CMEPrograms.CMEProgramSalesRep = Users.UserID
WHERE CMEProgramSym.CMEProgramID = '#URL.ID#'
ORDER by Abs(DateDiff(s, CMEProgramSym.CMEProgramSymDate, [TimeStamp])) ASC
</CFQUERY>


The CMEProgramSymDate field is the date field that will help sort the order.

Well I got the query to work, but the order is very messed up now even though its correct by the datediff function.

08/11/04
08/3/04
08/18/04
07/21/04
07/14/04
09/09/04
07/08/04
09/22/04

I was hoping for it to start here at the 8/11 and move on forward. so at least I can have the most current meeting at the top.

Not really.  jeffmace mentioned "How can I display the item that is closest to todays date" so in that case Now() would work.

CMEProgramSymDate is smalldatetime in SQL and it looks like this in the database '5/24/2003'  

Maybe that will help clear up the issue, I am not sure if there is a different way to code for that setup.

Jeff

@Jeff

It looks like you dont have a Date Format without time?
You could try to use "mi" instead of "s" for the DateDiff Function.

Also you could check out:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp

Quote from site: "...Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0."


@mriichmon

Sorry, i got it wrong because i read "list the item closes to August 8th..." and i really didnt notice that this was the post date :) I'm sorry, you were right

it's ok :o)