VirtualObjectives

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:

  1. Download or copy the following script.
  2. Change the date format in the script to your requirements (Default is DD/MM/YYYY)
  3. Change @DateLength to the required size.
  4. 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:

Download Check_Date_Format.zip