Rounding issue converting float to datetime

• Wolfmeister

  SSCommitted

  Points: 1547

  More actions

  March 21, 2011 at 9:51 am

  #235368

  Hi

  I have a scenario where I need to take the date part from one datetime field, and the time part from another datetime field, and concatenate them together - so '2011-01-01 13:17:14.000' and '1899-12-30 14:30:00.000' would result in '2011-01-01 14:30:00.000'.

  The approach I have taken rather than string manipulation is as follows:

  1). for the date field, cast as float and then to int (to avoid rounding issues)

  2). For the time field, cast to float and use dateadd to add or subtract the number of days to 1900-01-01, so that the integer part is always 0.

  3). Add the two fields together.

  4). Cast back to datetime.

  For the most part this is working perfectly, but what I can't figure out is why in some cases, the time appears to be rounded down - using the above dates, I am getting '2011-01-01 14:29:59.997'. My code is below - any help appreciated!

  [font="Courier New"]DECLARE

  @ApptStartDate varchar(25) = '2011-01-01 13:17:14.000'

  ,@ApptStartTime varchar(25) = '1899-12-30 14:30:00.000'

  ,@ApptStartDate2 datetime

  ,@ApptStartTime2 datetime

  select @ApptStartDate2 = CONVERT(datetime,@ApptStartDate,121)

  select @ApptStartTime2 = CONVERT(datetime,@ApptStartTime,121)

  select

  cast(

  cast(cast(@ApptStartDate2 as float) as int) --Date part

  +

  cast(dateadd(day,datediff(day,@ApptStartTime2,'1900-01-01'),@ApptStartTime2) as float) --Time part

  as datetime)[/font]

  Thanks

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  March 21, 2011 at 10:18 am

  #1301397

  declare

  @StartDate datetime= '2011-11-17 13:17:14.000' ,

  @StartTime datetime = '1899-12-30 14:34:47.993'

  select

  DatePlusTime =

  -- Date as of midnight

  dateadd(dd,datediff(dd,0,@StartDate),0)+

  -- Time as offset from 1900-01-01 00:00:00.000

  @StartTime-dateadd(dd,datediff(dd,0,@StartTime),0)

  DatePlusTime

  ------------------------

  2011-11-17 14:34:47.993

• Wolfmeister

  SSCommitted

  Points: 1547

  More actions

  March 21, 2011 at 10:40 am

  #1301414

  Thanks Michael. Much appreciated 🙂

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  March 21, 2011 at 10:40 am

  #1301417

  [Opinion] Simpler-to-read syntax:

  DECLARE @StartDate DATETIME = '2011-11-17 03:17:14.000',

  @StartTime DATETIME = '1899-12-30 04:34:47.993'

  SELECT CAST(CAST(@StartDate AS DATE) AS DATETIME) + CAST(@StartTime AS TIME)

  Whichever method you choose make sure you test for performance.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Wolfmeister

  SSCommitted

  Points: 1547

  More actions

  March 21, 2011 at 10:47 am

  #1301421

  Thanks opc.three. I will test.