Changing the SQL Server Century Cut-Off Date

The work undertaken to resolve the year 2000 problem taught developers that they should be using four-digit dates. However, as integration with legacy systems is still required, so are two-digit dates. SQL Server provides a century cut-off point to help.

What is the Cut-Off Date?

People like to work with two-digit years. This means that user input or data from integrated legacy systems often provides datetime information with a two-digit year. For example, 89 for 1989 or 07 for 2007. For recent historical and future dates this is easily understood. However, when dates become more distant, some ambiguity is possible. For example, does 57 represent 1957 or 2057?
To allow dates to be automatically expanded from two digits to the four they will be processed and stored as, SQL Server implements a cut-off year. By default, in SQL Server 2005, this is 2049, meaning values 00 - 49 represent 2000 to 2049 and 50 - 99 are the years 1950 to 1999. The switchover point can be modified for a server if the range is unsuitable for the required application.

Viewing the Cut-Off Date

The current value of the cut-off date can be determined by executing the sp_configure stored procedure The cut-off date is an advanced option in SQL Server 2005 so advanced options must be switched on using the stored procedure first. To turn on advanced options, execute the following statements:

sp_configure 'show advanced options', 1 reconfigure

You can now view the current cut-off year by executing the following statement, which shows the available range and the currently configured and running values.

sp_configure 'two digit year cutoff'

NB: You will see a configured value and the current 'run' value. The run value is set to the configured value with the reconfigure command.

Changing the Cut-Off Date

Changing the configured cut-off date uses the same sp_configure statement but with an additional parameter for the new value. This must be within the range shown by the command executed above. Executing sp_configure changes the configured year but not the running value so you also need to execute a 'reconfigure' command to use the new cut-off.

sp_configure 'two digit year cutoff', 2069 reconfigure SELECT convert(DATETIME, '31 Dec 69')   -- Year 2069 SELECT convert(DATETIME, '01 Jan 70')   -- Year 1970 Run Query on SQL Server      Right Click on Object Explorer and select Properties   Click on Advance   Change property "Two Digit Year Cutoff" and click OK.    Re-run the same Query

Modify list of directories name from Command Prompt

I have one requirement to modify name for list of directories through command prompt. Operating system is Windows Server 2008 R2.
Example - I do have following directory structure

• ROOT
  • 123#5
    • 11
    • 22
  • 123#6
    • 11
    • 22
    • 33
  • 123#7
  • 123#8
    • 44

I want to update above mentioned directory structure as following structure through batch file or command prompt.

• ROOT
  • 123~5
    • 11
    • 22
  • 123~6
    • 11
    • 22
    • 33
  • 123~7
  • 123~8
    • 44 
    
    Batch File  
    
    

    @echo off
    
    setlocal EnableDelayedExpansion
    
    for /r "C:\root" %%d in (.) do (
      set dirname=%%~nxd
      echo !dirname! | findstr "#" >nul
      if !errorlevel! equ 0 (
        set newname=!dirname:#=~!
        ren "%%~dpnd" "!newname!"
      )
    )
    
    endlocal

InitCap / String Capitalization Function

The first character in each word is converted to uppercase while the rest of the characters of the word are all made to lowercase.  The same is the case for the word "MINI", as can be seen from the book titles "A Practical Guide To Project Management". Instead of maintaining it to all capital letters, it was changed to "Mini".  Since the user-defined function won't be able to know which words need to be retained as all capital letters, these special cases have to be handled manually.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) )

RETURNS VARCHAR(4000)

AS

BEGIN

DECLARE @Index          INT

DECLARE @Char           CHAR(1)

DECLARE @PrevChar       CHAR(1)

DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)

SET @Index = 1

WHILE @Index <= LEN(@InputString)

BEGIN

    SET @Char     = SUBSTRING(@InputString, @Index, 1)

    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '

                         ELSE SUBSTRING(@InputString, @Index - 1, 1)

                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')

    BEGIN

        IF @PrevChar != '''' OR UPPER(@Char) != 'S'

            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))

    END

    SET @Index = @Index + 1

END

RETURN @OutputString

END