Be aware when you use date to filter the records

 When you execute the below query, as you expect, the result will be “September 26^th 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 26^th 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 26^th 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.”

Read More

How to insert values into an Identity column in SQL Server

Identity is a property that can be set on a column. When an identity is enabled the column will be populated automatically by system and it will not allow user to enter value explicitly however there is an option in SQL server SET IDENTITY_INSERT.

Let’s create table to explain in details.

CREATE TABLE dbo.ErrorLog (LogId INT IDENTITY(1,1), LogMsg VARCHAR(MAX))
INSERT INTO ErrorLog (LogMsg) VALUES('a') GO 10 SELECT * FROM dbo.ErrorLog

The test table is created and populated with some sample records. Let’s insert a record into the table and specify value for the LogId column explicitly.

INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(0,'b')

As expected we get the error message Cannot insert explicit value for identity column in table 'ErrorLog' when IDENTITY_INSERT is set to OFF. There are situations where we need to enter value into the Identity enabled column. To do that we must turn on the IDENTITY_INSERT property as in below example. The identity_insert property must be turned off after the insert otherwise it always expect you to specify value for the identity enabled column with in the session.

SET IDENTITY_INSERT dbo.ErrorLog ON
INSERT INTO dbo.ErrorLog(LogId,LogMsg) VALUES(20,'b')
SET IDENTITY_INSERT dbo.ErrorLog OFF

Read More

Concatenate column values from multiple rows into a single column in SQL Server

I have created a simple table to demonstrate how to concatenate the values from multiple rows in to one column.

CREATE TABLE #tblProduct (product_Id INT IDENTITY(1,1), product_name VARCHAR(10))

Insert some sample records in to this temporary table

INSERT INTO #tblProduct (product_name) VALUES('AAA')
INSERT INTO #tblProduct (product_name) VALUES('BBB')
INSERT INTO #tblProduct (product_name) VALUES('CCC')
INSERT INTO #tblProduct (product_name) VALUES('DDD')
INSERT INTO #tblProduct (product_name) VALUES('EEE')
INSERT INTO #tblProduct (product_name) VALUES('FFF')
INSERT INTO #tblProduct (product_name) VALUES('GGG')


Once you have populated the table with data, Run this query to get the row values in to one column.

DECLARE @productarr VARCHAR(8000) SET @productarr = '' SELECT @productarr = @productarr + '"' + CAST(product_Id AS VARCHAR) + '",' FROM #TBLPRODUCT ORDER BY product_Id
SELECT LEFT(@productarr,LEN(@productarr)-1)
DROP TABLE #tblProduct

That’s all you have done with the job

Read More

How to find which query is currently running in SQL Server 2000.

The below given function will return the currently running query for the process id.

STEP1: Create the below function in your server database.

CREATE FUNCTION SHOW_MY_PROCESS (@SPID INT)
RETURNS VARCHAR(8000)AS
BEGIN
DECLARE @sql_handle BINARY(20), @handle_found BIT
DECLARE @stmt_start INT, @stmt_end INT
DECLARE @line NVARCHAR(4000), @wait_str VARCHAR(8)
DECLARE @sql_process AS VARCHAR(8000)
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.SYSPROCESSES
WHERE spid = @spid AND ecid = 0
SELECT @sql_process = SUBSTRING(TEXT, COALESCE(NULLIF(@stmt_start, 0), 1), CASE @stmt_end WHEN -1 THEN DATALENGTH(TEXT) ELSE (@stmt_end - @stmt_start)END)
FROM ::fn_get_sql(@sql_handle)
RETURN @sql_process
END

STEP2: Run the below query

SELECT dbo.SHOW_MY_PROCESS(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE SPID > 50

Read More

How to find the Date is first day of the Week in SQL.

Some time you asked to find the given date is first day of the week or not? here is the code.

IF DATEPART(DW,GETDATE()) = @@DATEFIRST
BEGIN
PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS FIRST DAY OF THE WEEK'
END
ELSE
BEGIN
PRINT CONVERT(VARCHAR,GETDATE(),110) + ' IS NOT A FIRST DAY OF THE WEEK'
END

Read More

How to find whether the date is end date of the month in SQL

To know the given date is end date of the month, You can use the dateadd and day functions. that is, you should add 1 day to the given date and check whether the result date's day is 1 or not. If the result date's day is one then the given date is end date of the month otherwise it is not the end date of the month.

SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIOD

Read More