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?

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  March 2, 2004 at 8:35 pm

  #496794

  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

  March 4, 2004 at 9:19 am

  #497331

  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