Be aware when you use date to filter the records

 When you execute the below query, as you expect, the result will be “September 26th 2049”

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/49'
SELECT @DATE

Now, Lets see what happen when we pass 50 instead of 49

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/50'
SELECT @DATE

You will get the result as “September 26th 1950”; this is because by default, SQL Server interprets two-digit years based on a cutoff year of 2049.

DECLARE @DATE AS DATETIME
SET @DATE = '09/26/2050'
SELECT @DATE

Now, as you expect you will get the result as “September 26th 2050”; so it is always good to specify the four-digit year.

Notes from BOL

“By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.”

How to insert values into an Identity column in SQL Server

If the column is defined as identity then SQL Server will automatically insert the values in to identity column whenever new records get inserted. In some situation we may need to insert values into the identity column explicitly. In the following section I have demonstrated how we can achieve this. For demonstration purpose let’s create two tables with identity column and populate with some records.

CREATE TABLE TBLIDENTITY (IID INT IDENTITY (1, 1), ENAME VARCHAR (20) DEFAULT 't-s-q-l.BlogSpot')

INSERT INTO TBLIDENTITY DEFAULT VALUES
INSERT INTO TBLIDENTITY DEFAULT VALUES
INSERT INTO TBLIDENTITY DEFAULT VALUES

CREATE TABLE TBLIDENTITY_TEMP (IID INT IDENTITY (4, 1), ENAME VARCHAR (10) DEFAULT 't-s-q-l')

INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES
INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES
INSERT INTO TBLIDENTITY_TEMP DEFAULT VALUES

let’s assume, we want to insert the records from the table TBLIDENTITY_TEMP to TBLIDENTITY whilst we need to preserve the IID column value of TBLIDENTITY_TEMP in TBLIDENTITY. In order to do this, we have to set IDENTITY_INSERT ON.

SET IDENTITY_INSERT TBLIDENTITY ON
INSERT INTO TBLIDENTITY (IID, ENAME)
SELECT * FROM TBLIDENTITY_TEMP
SET IDENTITY_INSERT TBLIDENTITY OFF