Working with dates and times in SQL

Working with dates and times in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 5/25/2016
-- working with dates

USE Northwind

-- dates/times are very important to organizations
-- one of the fundamental things that a system stores
-- is when something occurred

-- dates/times are complicated in real life
-- different calendars, leap years, time zones, etc.

-- don't try to do your own date/time calculations
-- it's time consuming, and you're likely to get it wrong

-- advice: get to know how dates/times work 
--   on the platform you are using

-- this video will be using the datetime2 data type
-- however, the concepts apply to datetime and smalldatetime

-- something simple
SELECT GETDATE() AS [Current System Date & Time]

-- see that there are many pieces to 
-- year, month, day of month
-- hour (24), minute, second, millisecond

-- to get any single piece, you use this function

SELECT DATEPART(YEAR,   GETDATE()) AS [Year],
       DATEPART(MONTH,  GETDATE()) AS [Month],
       DATEPART(DAY,    GETDATE()) AS [Day of Month],
       DATEPART(HOUR,   GETDATE()) AS [Hour],
       DATEPART(MINUTE, GETDATE()) AS [Minute],
       DATEPART(SECOND, GETDATE()) AS [Second],
       DATEPART(MS,     GETDATE()) AS [Millisecond]

-- and there are some additional convenience functions
SELECT YEAR(GETDATE())  AS [Year],
       MONTH(GETDATE()) AS [Month],
       DAY(GETDATE())   AS [Day of Month]
       -- not one for hour
       -- not one for minute
       -- not one for second
       -- not one for millisecond

-- note that these are all whole numbers,
-- i.e., exact, integer values
-- they represent a *part* of the date/time
-- they *don't* represent a duration or period of time
-- so you'll never see a "7.2 days" using DATEPART

-- so let's see this work
SELECT   orderdate
FROM     orders
WHERE    YEAR(orderdate)  = 1997
  AND    MONTH(orderdate) = 2
ORDER BY orderdate

-- note that there are performance implications
-- for the next video

-- so how to create a new datetime2?
SELECT DATETIME2FROMPARTS(2016, 2, 15,17,30,1,0,0)
         AS [Feb 15 2016 5:30PM and 1 second]

-- it is possible to *implicitly* make a datetime2
--  starting with a string representation

DECLARE @d  AS datetime2
SET @d = '2016-02-15 17:30:01' -- implicit conversion
SELECT @d AS [datetime2 implicitly converted from varchar]

-- sql server will implicitly convert from
-- a wide variety of formats

-- generally a dangerous thing to do
-- prone to error
-- be *explicit*

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

-- what about moving forward or back in time
-- there is a function for that: DATEADD

SELECT   DATEADD(MONTH,          7, GETDATE()) AS [7 months from now],
         DATEADD(DAY,           77, GETDATE()) AS [77 days from now],
         DATEADD(YEAR,         -14, GETDATE()) AS [14 years ago],
         DATEADD(MILLISECOND, 3500, GETDATE()) AS [3.5 seconds from now],
         DATEADD(QUARTER,      -20, GETDATE()) AS [20 quarters ago],
         DATEADD(WEEKDAY,       37, GETDATE()) AS [37 weekdays from now]

-- the arguments are:
--    first: time unit to be used, by namme
--    second: positive or negative integer
--             meaning number of time units added/subtracted
--    third: a datetime2 expression - the starting point in time

-- note the milliseconds example above
-- see the difference here
SELECT   GETDATE()                       AS [now],
         DATEADD(SECOND, 3.5, GETDATE()) AS [3 seconds from now]

-- the 3.5 is onverted from a floating point to an integer
-- so the 0.5 is effectively ignored

-- how do we find the difference between two dates?
-- we use the datediff function

SELECT orderid,
       orderdate,
       shippedDate,
       DATEDIFF(day,  orderdate, shippedDate) AS [days until shipped],
       DATEDIFF(week, orderdate, shippedDate) AS [weeks until shipped]
FROM   orders

-- the arguments are:
-- first:  the unit of time measure, by name
-- second: the first date
-- third:  the second date

-- you might notice something odd in the weeks column
-- note orderid 10249 (second record)
-- days are 5, but it shows 1 week

SELECT orderid,
       orderdate,
       DATEPART(WEEKDAY,orderdate)            AS [weekday of order],
       shippedDate,
       DATEPART(WEEKDAY, shippeddate)         AS [weekday shipped],
       DATEDIFF(day,  orderdate, shippedDate) AS [days until shipped],
       DATEDIFF(week, orderdate, shippedDate) AS [weeks until shipped]
FROM   orders
WHERE  orderID = 10249

-- DATEDIFF gives a count of 
-- the number of time unit *boundaries* passed between the two dates
-- since the orderdate weekday is 6
-- and the shippedDate weekday is 4
-- a single week boundary was passed
-- i.e., 6, 7, *boundary*, 1, 2, 3, 4

--DATEDIFF returns a COUNT of boundaries passed
-- it does NOT measure time (or at least not accurately...)

-- in summary:
--  datetime2, datetime, smalldatetime, etc. are complex data types
--  use the functions provided - don't try to roll-your-own calculations
--  GETDATE gives current system time
--  DATEADD moves forward or back from a given point in time
--  DATEDIFF gives the number of time unit boundaries passed between two datetimes

-- Database by Doug
-- Douglas Kline
-- 5/25/2016
-- working with dates