Converting datetime to a numerical number

Question

Converting datetime to a numerical number

Brendon

Ten Centuries

Points: 1252
More actions
March 2, 2004 at 7:45 pm

#107314

Hi,
I'm trying to convert a datetime field to a numerical - my objective is to obtain a numerical number which I can sort on, instead of a date field.
At the moment I have select convert(int, mydate) from table
The problem is that the numerical numbering seems a little out.
eg
2004-02-27 15:44:03.000 27/02/2004 38043
2004-02-27 15:42:22.000 27/02/2004 38043
2004-02-27 15:42:03.000 27/02/2004 38043
2004-02-26 23:29:29.000 26/02/2004 38042
2004-02-26 23:33:02.000 26/02/2004 38042
2004-02-26 23:34:53.000 26/02/2004 38042
2004-02-26 23:28:00.000 26/02/2004 38042
2004-02-27 10:26:05.000 27/02/2004 38042
2004-02-27 11:08:47.000 27/02/2004 38042
2004-02-27 11:41:25.000 27/02/2004 38042
This list is sorted by numerical number descending - notice that the 27th appears below the 26th? How can I fix this?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Brendon,

If your dates are, in fact, stored as SQL DateTime datatypes, there is no need to convert the dates to numeric representations to have them sort correctly. Just do an ORDER BY on the date column.

If you insist on converting datetimes to numeric values, use CONVERT(FLOAT,mydate). These reason the use of INT was, in your words, "a little out", is because INT rounds up or down based on the decimal portion (the time-of-day portion) of the underlying date.

If you wish to drop the decimal portion (the time) of the date for the sort, use FLOOR(CONVERT(FLOAT,mydate)). If you do drop the time portion of the date, don't expect the times to be in any particular order for any given date.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Jakob SSCommitted Points: 1720 More actions · Answer 2

Another approch is to just use a "delta time" between the test date and a predefined date (e.g., '1/1/2000') [in whatever units you want] and sort on the difference value.

For example, if "seconds" will give you enough granularity, you could calculate a "DATEDIFF" delta for each date like this:

-- Determine Number of Seconds Since 1/1/2000:

DECLARE @MyTestDate DATETIME

SET @MyTestDate = '3/1/2004'

SELECT DATEDIFF(second, '1/1/2000', @MyTestDate)

Result:

131414400

and sort on the DATEDIFF values...

HTH.

- john