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

Advanced use of the LIKE operator in SQL

Advanced use of the LIKE operator in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 5/19/2016
-- LIKE, advanced

use northwind

-- some advanced things to do with LIKE
-- if you are new to using LIKE, see my earlier video
-- previous video covers simple wildcard matching with % and _

-- we'll cover character sets
-- and specific use cases, i.e., how to do....

-- let's start with a simple example from the products table

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
ORDER BY ProductName

-- notice that there are 77 products

-- now, let's limit that to all the products
-- whose names start with the letter A

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE 'a%'
ORDER BY ProductName

-- what about begins with A or B or C
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE 'a%'
  OR     ProductName LIKE 'b%'
  OR     ProductName LIKE 'c%'
ORDER BY ProductName

-- can also be phrased as

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[abc]%'
ORDER BY ProductName

-- the square brackets mean:
-- "a single character from within this set"

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[abc][lho]%'
ORDER BY ProductName

-- this one means
-- first letter is from this set: [abc]
-- second letter is from this set: [lho]
-- then zero or more characters after the first two letters

-- this can also be phrased as a "list", like this:

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[a-c][lho]%'
ORDER BY ProductName

-- the hyphen means,
-- all characters alphabetically between the two letters
-- what about this?

-- products whose name does not start with
-- an a or b or c
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    NOT   (ProductName LIKE 'a%'
            OR  ProductName LIKE 'b%'
            OR  ProductName LIKE 'c%')
ORDER BY ProductName

-- another option:

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName NOT LIKE '[abc]%' -- using NOT
ORDER BY ProductName

-- can be rephrased like this:
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[^abc]%'
ORDER BY ProductName

-- note the carat symbol ^ inside the brackets
-- this means
-- "any single character not in this list"

-- and this:
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[^abc][^lho]%'
ORDER BY ProductName

-- this one means
-- first letter is NOT from this set: [abc]
-- second letter is NOT from this set: [lho]
-- then zero or more characters after the first two letters

-- another 
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '[a-ce-j]%' -- skips d
ORDER BY ProductName
-- means single letter in the set of letters
-- a thru c OR e thru j
-- then zero or more letters

-- so let's go through some common uses

-- number of letters
-- has at least 6 characters
SELECT   'match'   as [test]
WHERE    '1234567' LIKE '______%' -- 6 underscores

SELECT   'match'   as [test]
WHERE    '12345'   LIKE '______%' -- 6 underscores

-- begins with a letter
SELECT   'match'
WHERE    'product' LIKE '[a-z]%'

SELECT   'match'
WHERE    '3product' LIKE '[a-z]%'


-- begins with a letter
-- and is at least 6 characters
SELECT   'match'
WHERE    'a12345' LIKE '[a-z]_____%' -- 5 underscores

SELECT   'match'
WHERE    'a1234' LIKE '[a-z]_____%' -- 5 underscores

SELECT   'match'
WHERE    '012345' LIKE '[a-z]_____%' -- 5 underscores

-- begins with a number
SELECT   'match'
WHERE     '0abcdefgh' LIKE '[0-9]______%' -- 6 underscores


-- begins with a letter, contains a number
SELECT   'match'
WHERE    'abc5' LIKE '[a-z]%[0-9]%'

-- ending / trailing spaces

SELECT   'match'
WHERE    ' abc' LIKE ' %'

-- ending / trailing punctuation

SELECT   'match'
WHERE    '?abc345' LIKE '[!@#$%?,.]%'

-- whitespace char, i.e., tab

-- note that char(9) returns the ASCII tab character
-- also note that + is the concatenation operator
-- thus the format string is square brackets
-- with a space and a tab character
-- it checks for tabs or spaces

SELECT   'match'
WHERE    'db' + char(9) + 'Doug' LIKE '%[ ' + char(9) + ']%'

-- detect punctuation character
-- ASCII has 
SELECT   'match'
WHERE    'abc{abc' 
   LIKE  '%[' 
         + char(33) + '-' + char(47) -- ASCII '!' thru '/'
         + char(58) + '-' + char(64) -- ASCII ':' thru '@'
         + char(91) + '-' + char(96) -- ASCII '[' thru '`'
         + char(123)+ '-' + char(126)-- ASCII '{' thru '~'
         + ']%'

SELECT   'match'
WHERE    'abc]abc' 
   LIKE  '%[' 
         + char(33) + '-' + char(47) -- ASCII '!' thru '/'
         + char(58) + '-' + char(64) -- ASCII ':' thru '@'
         + char(91) + '-' + char(96) -- ASCII '[' thru '`'
         + char(123)+ '-' + char(126)-- ASCII '{' thru '~'
         + ']%'


-- contains unprintable characters
SELECT   'match'
WHERE    'abc'
         + char(7) -- bell character
         + 'abc' 
   LIKE  '%[^'  -- note the carat here
         + char(32) + '-' + char(126)
         + ']%'

-- same technique can be used for whitespace characters
-- same technique can be used for unicode character set

-- Database by Doug
-- Douglas Kline
-- 5/19/2016
-- LIKE, advanced

The SQL LIKE Operator

A short video covering the LIKE operator in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 5/11/2016
-- LIKE

use northwind

-- the LIKE statement is a logical operator that evaluates
-- if a set of characters matches a pattern
-- if it matches, it returns true
-- otherwise it returns false

-- since LIKE returns a logical value,
-- it is common to use LIKE in the WHERE clause, 
-- which expects logical expressions

-- if you don't know the WHERE clause,
-- check out another video on my channel

-- let's start with a simple example from the products table

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
ORDER BY ProductName

-- notice that there are 77 products

-- now, let's limit that to all the products
-- whose names start with the letter A

SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE 'a%'
ORDER BY ProductName

-- now all the products whose names end in A
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '%a'
ORDER BY ProductName

-- now all the products that have an A anywhere
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName LIKE '%a%'
ORDER BY ProductName

-- and all the ones without an A anywhere
SELECT   ProductName,
         ProductID,
         Unitprice
FROM     Products
WHERE    ProductName NOT LIKE '%a%'
ORDER BY ProductName

-- as you can see, the % character is special
-- it's a "wildcard" character, i.e.,
-- it can "match" on characters

-- before we go on, the general format of the LIKE operator is

-- [characterExpression] LIKE [formatExpression]

-- the [characterExpression] is the part that will be tested
-- the [formatExpression] defines the "test", or the format
--     used to test the [characterExpression] 

-- in the example above:
 --  ProductName LIKE '%a%'
 -- ProductName is the [characterExpression]
 -- '%a%' is the [formatExpression]

 -- ProductName            LIKE   '%a%'
 -- [characterExpression]  LIKE   [formatExpression]
 -- 

 -- its best to think of LIKE as a comparison operator, 
 -- similar to <, >, <=, >=, !=, etc.
 -- e.g., WHERE age > 35
-- the expressions on the left and right are compared,
--  based on a set of rules

-- there are two main special characters for LIKE

-- special character #1: % can match on zero or more characters

-- for demonstration purposes
-- a record with the word Match! is shown if the WHERE
-- clause evaluates to true
 
SELECT 'Match!'
WHERE  1=1

SELECT 'Match!'
WHERE  1=2

-- some examples...

SELECT 'Match!'
WHERE  'Fred' LIKE 'F%'

-- yes, Fred starts with F
-- the F matches on F, 
--  and the % matches on 'red'

SELECT 'Match!'
WHERE  'Fred' LIKE 'S%' 

-- no Fred doesn't start with S

 -- note that LIKE is case insensitive
SELECT 'Match!'
WHERE  'Fred' LIKE 'f%'

SELECT 'Match!'
WHERE  'fred' LIKE 'F%'

-- what about this
SELECT 'Match!'
WHERE  'F' LIKE 'F%'

-- yes, it matches
-- in this case, the F matches on 'F'
-- and the '%' matches on '', i.e., nothing

-- again, % matches on *zero* or more characters

-- how about this one

SELECT 'Match!'
WHERE  'Fred' LIKE 'F%d'

-- must start with F and end with d, any number of characters

-- or this

SELECT 'Match!'
WHERE  'F' LIKE 'F%'

-- or this

SELECT 'Match!'
WHERE  'Fred' LIKE '%r%'

-- must contain an r, any number of characters

-- or this

SELECT 'Match!'
WHERE  'Fred' LIKE '%f%'

-- must contain an f, any number of characters

-- or this

SELECT 'Match!'
WHERE  'F' LIKE '%ed'

-- must end in ed, any number of characters

-- and of course, most characters are NOT special
-- and must match exactly

SELECT 'Match!'
WHERE  'Fred' LIKE 'Fred'

-- but with no special characters 
-- it may make more sense to do this:

SELECT 'Match!'
WHERE  'Fred' = 'Fred'

-- special character #2: _ (underscore) matches exactly one character

SELECT 'Match!'
WHERE  'Fred' LIKE '____' -- 4 underscores

-- must have exactly 4 characters

SELECT 'Match!'
WHERE  '1F7.' LIKE '____' -- 4 underscores

-- must have exactly 4 characters

SELECT 'Match!'
WHERE  'Fred' LIKE '___' -- 3 underscores

-- must have exactly 3 characters

SELECT 'Match!'
WHERE  'Fred' LIKE 'F___' -- 3 underscores

-- must start with F, and have exactly 4 characters

SELECT 'Match!'
WHERE  'Fred' LIKE 'F__d' -- 2 underscores

-- must start with F, end in d, and have exactly 4 characters

-- combining the special characters

SELECT 'Match!'
WHERE  'Frederick' LIKE 'F__%' -- 2 underscores

-- must start with F and have at least 3 characters

SELECT 'Match!'
WHERE  'Frederick' LIKE '%_d_%'

-- must contain a d
-- d must not be the first or last letter
-- implies at least 3 characters

-- in summary
-- LIKE is a comparison operator
-- is compares a character string to a format string
-- % is a special character that matches on zero or more characters
-- _ is a special character that matches on exactly one character
-- other characters must match exactly
-- remember, digits and most punctuation are characters

-- thanks for watching

-- Database by Doug
-- Douglas Kline
-- 5/11/2016
-- LIKE