Check Date Format

This script is used to compare date format of the SQL Server with a predefined date format.
It is used to check that the date format of the SQL server has not changed.

Why? you may ask.. well.. the Regional Settings (and hence the date format) can change if the account with which the SQL Server service is using has changed. This can happen if your Windows domain administrator (by accident or on purpose) changes the account, or makes it a roaming profile, or several other reasons.

When this happens the Regional Settings will most often change back to US format. If you are using third-party applications on the SQL Server which were installed and/or require a specific date format to input or output data for example, then the date format will consequently change.

This should not usually pose a problem, however, it is possible for things like a Data Transformation Task in a DTS packages to "work out" a date format from an input file. When this happens the Day, 2 digit Month and 2 digit Year can be interpreted incorrectly resulting in bad data.

For Example:

If the source file contains data with dates as DD/MM/YY like 25/03/06 then the DTS package can interpret it as YY/MM/DD resulting in 6th March 2025

A simple solution would be to specify SET DATEFORMAT dmy at the beginning of a SQL script or step. However, you will need to do this for ALL your jobs and DTS packages which may involve a lot of work. An easier method is to detect that the date format has changed and raise an error to alert the administrators so that they can change the date back.

Implementation:

Download or copy the following script.
Change the date format in the script to your requirements (Default is DD/MM/YYYY)
Change @DateLength to the required size.
You can then...
* Add it as a preliminary step in a specific DTS package OR..
* Insert it as the first step in a Job OR..
* Create a daily scheduled job to check the date format
* etc..

Download/Script:

Check_Date_Format.zip

-------------------------------------------------------------------------------
-- Check date format.sql
-- Compare date format of the SQL Server with a predefined date format.
-- Used to check that the date format of the SQL server has not changed.
-- 05/03/2007 - John Buoro - Script created.
-------------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @DateSQL varchar(255)
DECLARE @DateSys varchar(255)
DECLARE @msg varchar(1000)
DECLARE @DateLength int

-------------------------------------------------------------------------------
-- Get current date format.
-- Formatted to DD/MM/YYYY - Change this section to suit your date format requirements.
SET @DateLength = 10
SET @DateSQL = ( select 
	RIGHT('00' + LTRIM(STR(DATEPART(dd, GETDATE()))),2) + '/' + -- dd
	RIGHT('00' + LTRIM(STR(DATEPART(mm, GETDATE()))),2) + '/' + -- mm
	LTRIM(STR(DATEPART(yyyy, GETDATE()))) ) -- yyyy
PRINT 'Current Required Date = '+ @DateSQL

-------------------------------------------------------------------------------
-- Get the current system date.
CREATE TABLE #dttmp(info VARCHAR(100))
INSERT #dttmp EXEC master..xp_cmdshell 'echo %date%'
SET @DateSys = RIGHT((SELECT top 1 info FROM #dttmp), @DateLength)
PRINT 'Current System Date = ' + @DateSys

-------------------------------------------------------------------------------
-- Compare dates.
IF @DateSQL <> @DateSys
BEGIN
	SET @msg = 'ERROR: Regional date setting does not match the required format for 
		the account runnning the SQL Server service.' + 
		 ' Current Required Date = '+ @DateSQL + ' Current System Date = ' + @DateSys
	RAISERROR(@msg, 16, 1)
END
ELSE
BEGIN
	SET @msg = 'Regional date setting matches the required format for the account
		runnning the SQL Server service.' +
		 ' Current Required Date = '+ @DateSQL + ' Current System Date = ' + @DateSys
	PRINT @msg
END

SET NOCOUNT OFF

-------------------------------------------------------------------------------
-- Clean up.
drop table #dttmp

-------------------------------------------------------------------------------