Compare Date – SQL Server Syntax Example: Compare Date – T-SQL Example

Compare Date – SQL Server Syntax Example: Compare Date – T-SQL Example

Purpose: – Illustrates the for the .

SYNTAX:

SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
WHERE date1 comparison operator date2
[ INTO new_table ]
[ FROM { table_source } [ ,…n ] ]
[ WHERE search_condition AND search_condition ]
[ GROUP BY ]
[ HAVING search_condition ]

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.

View Other SQL Server Syntax Examples

-- 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

Sample Output for Compare Date Example

SQL Select Compare Date