Date stored as a Decimal

• nathan.kennedy

  SSC Journeyman

  Points: 92

  More actions

  January 27, 2009 at 12:01 am

  #128266

  Hello,

  I am querying a table and can get the required results all ok, however all of the date fields are stored in the decimal datatype format. Ie for a row of the date 27/01/2009 (dd/MM/yyyy) the actual filed contains this 131662107.

  I need to try and convert 131662107 to 27/01/2009 (dd/MM/yyyy)

  Any help would be appreciated.

  Kind Regards

  Nathan

• Michelle Ufford-459603

  SSCommitted

  Points: 1642

  More actions

  January 27, 2009 at 7:17 am

  #933161

  Try using Cast(yourDecimalColumn As datetime), i.e.

  Declare @myDate decimal;

  Set @myDate = 39838;

  Select Cast(@myDate As datetime);

  Regards,

  Michelle Ufford
  SQLFool.com - Adventures in SQL Tuning

• Maxim Picard

  SSCrazy

  Points: 2052

  More actions

  January 27, 2009 at 7:36 am

  #933179

  If the date is stored as a number it should not be this big!

  declare @DateTest datetime

  set dateformat dmy

  set @DateTest = '27/01/2009'

  select @DateTest

  ,convert(int,@DateTest) IntDate

  ,convert(decimal,@DateTest) DecimalDate

  The int for your '27/01/2009' date is 39838.

  Even if you push to the limit we are nowhere near 131662107...

  declare @DateTest datetime

  set dateformat dmy

  set @DateTest = '31/12/9999'

  select @DateTest

  ,convert(int,@DateTest) IntDate

  ,convert(decimal,@DateTest) DecimalDate

  Trying to convert 131662107 results in an arithmetic overflow

  declare @DecimalTest decimal

  set @DecimalTest = 131662107

  -- Arithmetic overflow...

  select convert(datetime,@DecimalTest)

  Missing a piece of the puzzle, maybe it's something I don't know about dates...