T-SQL Date Math

/*------------------------------------------------------------------------------
    WORKING WITH DATES AND MATH

    I'd previously been led to believe the only way to add and subtract from
    dates was to use the DATEADD() function. I recently discovered that's not
    true. The trick is to make sure the date you're working with is of type
    datetime, and not just date.

------------------------------------------------------------------------------*/

-- Here are some examples of subtracting time from a datetime value
-- Notice the math has to be decimal math, not integer, or it won't work
select getdate()
select cast('10-MAY-2017' as datetime)                          -- midnight
select cast('10-MAY-2017' as datetime) - 1*(1/24.0/60.0)        -- midnight minus 1 minute
select cast('10-MAY-2017' as datetime) - 1*(1/24.0/60.0/60.0)   -- midnight minus 1 second

-- Another thing I noticed in this testing was that SELECT and PRINT show
-- the same value two different ways. PRINT excludes the seconds and milliseconds
select getdate()
print getdate()

-- You can display the detailed data using PRINT if you convert it to a varchar,
-- but you cannot do math against a varchar. You can do math if you convert it to
-- datetime, but then you can't see the details again.
print convert(varchar(24),getdate(),109)                        -- shows seconds, no math
print convert(varchar(24),getdate(),109) - 1*(1/24.0/60.0)      -- this line won't work
print convert(datetime,getdate(),109) - 1*(1/24.0/60.0)         -- can math, no seconds

-- You can use the other datetime formats as well, but keep in mind the
-- specific limitations/features of each type.
select getdate()
select cast(getdate() as smalldatetime)                         -- rounds to nearest whole minute
select cast(getdate() as smalldatetime) - 1*(1/24.0/60.0)       -- subtract one minute
select cast(getdate() as smalldatetime) - 1*(1/24.0/60.0/60.0)  -- subtract one second, no difference