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

Get 5 Hours of FREE PREMIUM Videos:

LearnVisualStudio.NET Free Preview

LearnVisualStudio Premium Videos

dotNetVideos Premium Video Catalog

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

Purpose: – Illustrates the SQL Server syntax for the compare date.

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.

 T-SQL | 
 copy code |
?
 
-- 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

Related posts:

1. Date Example – Data Type – JavaScript Syntax – JS Date Example Date Example - Data Type - JavaScript Syntax - JS...
2. SQL Server 2005 Express Tutorial: (13 Videos) Database mirroring, backup sql server 2005, sql server 2005 replication, sql server standard edition, download sql server 2005, compare sql server, sql server instance Excellent SQL Server 2005 Express series of 13 free videos...
3. Bitwise – SQL Server Syntax Example: Bitwise – T-SQL Example Bitwise - SQL Server Syntax Example: Bitwise - T-SQL Example...
4. SQL Server / T-SQL Examples Here is a library of SQL Server / T-SQL Examples....
5. SQL Server School (17 Hours): Sql Server Connection, Sql Server Stored Procedure, Sql Server Profiler, Trusted Sql Server, Sql Query Analyzer, and More I have found these 17 hours of video tutorials from...

Related posts brought to you by Yet Another Related Posts Plugin.