SQL Server equivalent of adding hours to a date field (in oracle) using Trunc

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  January 12, 2018 at 2:20 pm

  #334477

  How can I do this in SQL server?

  select Trunc(createdate) + 3/24
  from Orders

  Thanks

• ryanbesko

  Ten Centuries

  Points: 1364

  More actions

  January 12, 2018 at 2:43 pm

  #1975796

  QuietCoder - Friday, January 12, 2018 2:20 PM

  How can I do this in SQL server?

  select Trunc(createdate) + 3/24
  from Orders

  Thanks

  SELECT DATEADD(HOUR, 3/24, GETDATE())
  Change GETDATE() to your date field of course.

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 12, 2018 at 2:52 pm

  #1975797

  ryanbesko - Friday, January 12, 2018 2:43 PM

  QuietCoder - Friday, January 12, 2018 2:20 PM

  How can I do this in SQL server?

  select Trunc(createdate) + 3/24
  from Orders

  Thanks

  SELECT DATEADD(HOUR, 3/24, GETDATE())
  Change GETDATE() to your date field of course.

  This will only return GETDATE() or the value in the datefield you pass. To add 3 hours the second argument should just be 3 (3/24 will just yield the integer division result, which is 0).

  With GETDATE(), you're adding an integer number of the specified unit. The unit is already hours, so we just add 3 of them. 

  Cheers!

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 12, 2018 at 2:56 pm

  #1975798

  ryanbesko - Friday, January 12, 2018 2:43 PM

  QuietCoder - Friday, January 12, 2018 2:20 PM

  How can I do this in SQL server?

  select Trunc(createdate) + 3/24
  from Orders

  Thanks

  SELECT DATEADD(HOUR, 3/24, GETDATE())
  Change GETDATE() to your date field of course.

  There are a couple of gotchas here.  First 3/24 uses integer division, so it equals 0.  Second, I assume that he wants to add 3 hours (3/24ths of a day) not 7.5 minutes (3/24ths of an hour).  Finally, I assume that TRUNC(createdate) gives you the date at midnight, so you need to make sure that your adding to midnight, not the current time.

  SELECT DATEADD(HOUR, 3, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  January 13, 2018 at 2:43 am

  #1975825

  The TRUNC(DATE,[FMT]) function will round the date to the nearest date depending on the format (fmt) parameter, the default is DAY. The return value is always DATE.
  😎

• QuietCoder

  SSChasing Mays

  Points: 621

  More actions

  January 15, 2018 at 8:55 am

  #1975910

  drew.allen - Friday, January 12, 2018 2:56 PM

  ryanbesko - Friday, January 12, 2018 2:43 PM

  QuietCoder - Friday, January 12, 2018 2:20 PM

  How can I do this in SQL server?

  select Trunc(createdate) + 3/24
  from Orders

  Thanks

  SELECT DATEADD(HOUR, 3/24, GETDATE())
  Change GETDATE() to your date field of course.

  There are a couple of gotchas here.  First 3/24 uses integer division, so it equals 0.  Second, I assume that he wants to add 3 hours (3/24ths of a day) not 7.5 minutes (3/24ths of an hour).  Finally, I assume that TRUNC(createdate) gives you the date at midnight, so you need to make sure that your adding to midnight, not the current time.

  SELECT DATEADD(HOUR, 3, CAST(CAST(GETDATE() AS DATE) AS DATETIME))

  Drew

  Thank you Drew!