LISTING 2: Code to Show the Effect of Spelled-Out
 Month and Dateformat Settings

INSERT dbo.TestDates
	VALUES('01 Sep 2002', '01 Sep 2002')
INSERT dbo.TestDates
	VALUES('01 September 2002', '01 September 2002')
INSERT dbo.TestDates
	VALUES('01 Sep 02', '01 Sep 02')
INSERT dbo.TestDates
	VALUES('01 September 02', '01 September 02')
INSERT dbo.TestDates
	VALUES('Sep 01 2002', 'Sep 01 2002')
INSERT dbo.TestDates -- You can add a comma between day and year.
	VALUES('Sep 01, 2002', 'Sep 01, 2002')
INSERT dbo.TestDates
	VALUES('September 01 2002', 'September 01 2002')
INSERT dbo.TestDates
	VALUES('September 01, 2002', 'September 01, 2002')
GO
-- Use the following query to review the last eight rows.
SELECT TOP 8 *
FROM dbo.TestDates
ORDER BY RowID DESC
GO

-- Be careful with a two-digit year, even when the month is spelled out.
INSERT dbo.TestDates
-- If all numbers are supplied with two digits, SQL 
Server assumes day before year.
	VALUES('01 September 02', '01 September 02')
-- This insert returns a date in Sept 01, 2002 format
GO
-- Use the following query to review the last row inserted.
SELECT TOP 1 *
FROM dbo.TestDates
ORDER BY RowID DESC
GO

/* Even if you change the dateformat to ymd, SQL Server treats this insert as day first, then year because 
the month is spelled out and SQL Server can't determine the two-digit year as easily as a four-digit year. 
When the month is spelled out, SQL Server disregards the dateformat setting. If all numbers are supplied 
with two digits, day comes first and year follows. */
SET DATEFORMAT ymd
GO
INSERT dbo.TestDates
	VALUES('02 September 01', '02 September 01')
GO
-- Use the following query to review the last row inserted.
SELECT TOP 1 *
FROM dbo.TestDates
ORDER BY RowID DESC
GO
-- Make sure you set the dateformat back to mdy.
SET DATEFORMAT mdy
GO