-- Compare Date example from http://idealprogrammer.com 
-- PURPOSE: Comparing Dates allows you to examine if date exists or  
--          if one date is less than, equal to, or greater than another date
--          The relational operators (< , <=, >, >=, <>),
--          comparison operators (=, < , <=, >, >=, <>, !< , !>) 
--          and logical operators and Boolean predicates 
--          (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS, and LIKE)
--          are all supported for all the date and time data types
--          Use DATEADD and DATEDIFF to add and subtract date and time data types
--          String literal formats affect the presentation of data in applications to users
--          but not the underlying integer storage format in SQL Server.
--  Here is a list of date and time data types:
--			# date
--			# time(n)
--			# datetime
--			# smalldatetime
--			# datetimeoffset(n)
--			# datetime2        
--  Here are ways to specify dates as string literals:
--  1. Unseparated String Format:
--     The date data can be specified by using four, six, or eight digits, an empty 
--     string, or a time value without a date value.
--     The six-digit or eight-digit strings are always interpreted as ymd. 
--     The month and day must always be two digits.
--     Example: [19]960420
--  2. ISO 8601 Format:
--     #  YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
--     #  YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)
--     Examples:
--     #  2004-05-23T14:25:10
--     #  2004-05-23T14:25:10.1234567+07:00
--  3. Alphabetical Format:
--     Commas are optional and capitalization is ignored.
--     Enclose the date and time data in single quotation marks (').
--     If you specify 2 digit year, the two digit year configuration option 
--     determines century.
--     If you leave off the day, the 1st is assumed.
--     Examples:
--     #  Apr[il] [15][,] 1996
--     # Apr[il] 15[,] [19]96
--     # Apr[il] 1996 [15]
--     #
--     # [15] Apr[il][,] 1996
--     # 15 Apr[il][,][19]96
--     # 15 [19]96 apr[il]
--     # [15] 1996 apr[il]
--     #
--     # 1996 APR[IL] [15]
--     # 1996 [15] APR[IL]
--  4. Numeric Date Formats:
--     SYNTAX: number separator number separator number  [time] [time]
--     where separator is one of these: , - / .
--     Examples:
--		# [0]5/15/[19]97 -- (mdy)
--		# [0]5-15-[19]97 -- (mdy)
--		# [0]5.15.[19]97 -- (mdy)
--		# [0]5/[19]97/16 -- (myd)
--		#
--
--		# 15/[0]5/[19]97 -- (dmy)
--		# 15/[19]96/[0]5 -- (dym)
--		# [19]96/15/[0]5 -- (ydm)
--		# [19]96/[0]5/15 -- (ymd)
--  5. Time Formats
--     Enclose times with single quote '
--     Examples:
--		# 15:30
--		# 15:30[:20:999]
--		# 15:30[:20.9]
--		# 5am
--		# 5 PM
--		# [0]5[:30:20:500]AM
 
--   
 
 
-- SYNTAX: WHERE date1 > date2
 
 
CREATE TABLE #Dates
    (
    dtBirthdate datetime2
    );
INSERT INTO #Dates(dtBirthdate)VALUES('2001-05-06 23:59:59.9999999');
INSERT INTO #Dates(dtBirthdate)VALUES('1994-06-07 00:00:00.0000000');
INSERT INTO #Dates(dtBirthdate)VALUES('1954-03-07 23:59:59.9999999');
INSERT INTO #Dates(dtBirthdate)VALUES('1927-04-08 00:00:00.0000000');
 
-- The following four SELECT statements show different ways to find
-- dates
--Use CONVERT.
SELECT dtBirthdate 
FROM #Dates 
WHERE CONVERT(date,dtBirthdate) = '1994-06-07';
 
--Use >= and < =.
SELECT dtBirthdate 
FROM #Dates 
WHERE dtBirthdate >= '1954-01-01 00:00:00.0000000' 
    AND dtBirthdate < = '2002-01-01 23:59:59.9999999';
 
--Use > and < .
SELECT dtBirthdate
FROM #Dates
WHERE dtBirthdate > '1954-01-01 23:59:59.9999999'
    AND dtBirthdate < '2002-01-01 00:00:00.0000000';
 
--Use BETWEEN AND.
SELECT dtBirthdate
FROM #Dates
WHERE dtBirthdate BETWEEN '1954-01-01 00:00:00.0000000'
    AND '2002-01-01 23:59:59.9999999';
DROP TABLE #Dates
GO