Thursday, September 29, 2011

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

1. If you haven't yet read the Ultimate Guide to Datetime Datatypes I would strongly suggest you to do it.

2. Understand that within SQL Server we can store DATETIME values between 01/01/1753 and 12/31/9999 only. If we try to enter a value which isn't this range then it would throw an error.

3. The way you handle dates will depend on the DATEFORMAT set for your login. Internally DATEFORMAT takes its value from SET LANGUAGE.

/*
This code block would throw an error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
*/
SET DATEFORMAT MDY
GO


DECLARE @dt VARCHAR(20)

--Format is Month Day Year. But we are passing 13 as month so it is expected to throw the error
SET @dt = '13/09/10 18:52:12'


SELECT CONVERT(DATETIME, @DT)
GO

--Now it will work!
SET DATEFORMAT DMY
GO


Declare @dt VARCHAR(20)
SET @dt = '13/09/10 18:52:12'


SELECT CONVERT(DATETIME, @DT)

Wednesday, September 28, 2011

Difference between two dates in SQL Server


If we want to get the difference between two dates in SQL Server then we need to make use of DATEDIFF function. Showed few variations using DATEDIFF in my forum response here. So thought of logging in my blog as well for future reference :)



DECLARE @StartDate DATETIME
SET @StartDate = '2011-01-01 10:15:00.000'


Declare @EndDate DATETIME
SET @EndDate = '2011-01-01 11:35:00.000'

--To get only Hours
SELECT DATEDIFF(hh, @StartDate,@EndDate ) AS [Hours];

--To get the result in Minutes
SELECT CAST(DATEDIFF(ss, @StartDate, @EndDate) AS DECIMAL(10, 0)) / 60 AS [Minutes];

--To get both Hours and Minutes
SELECT DATEDIFF(hh, @StartDate, @EndDate) AS [Hours],
DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @StartDate, @EndDate),@StartDate),@EndDate) AS [Minutes];

Monday, September 26, 2011

Script ALL SQL JOBs from SQL Server Management Studio


How to script all the SQL Jobs from SQL Server Management studio?

Step 1: Go to SQL Server Mgmt Studio.
Step 2: Expand SQL Server Agent >> Jobs
Step 3: Hit F7 (or) goto menu View >> Summary
Step 4: Now ALL jobs would be listed out there.
Step 5: Make use of Control key and choose all the JOBs you want to script. (OR) If you want to script everything press Ctrl + A
Step 6: Right click >> "Script Job as" >> "Create To" >> File

Now you can make use of this file to create these jobs in your destination SQL Server Database.



Sunday, September 25, 2011

Calculating PERCENTILE in SQL Server

How to find 25th percentile or 75th percentile of given set of numbers (records in table) in SQL Server 2005?

In this post I will show couple of ways of calculating Percentile. I am not a statistician so i just took the help of YouTube to learn on how Percentile is actually calculated. During that course I found two different formulas for calculating Percentile! So I have provided couple of methods to calculate Percentile based on both the formulas I learnt.


IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[tblPercentile]') AND type in (N'U'))
DROP TABLE [dbo].[tblPercentile]
GO


CREATE TABLE tblPercentile
(
Sno INT identity(1,1), iValue float
)
GO

--First set of sample records
Insert into tblPercentile
SELECT 2 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 5 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 8 UNION ALL SELECT 8 UNION ALL
SELECT 8 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 9 UNION ALL
SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 11 UNION ALL SELECT 12

--Second dataset for testing
--Insert into tblPercentile
-- SELECT 22 UNION ALL SELECT 22 UNION ALL
-- SELECT 23 UNION ALL SELECT 24 UNION ALL
-- SELECT 24 UNION ALL SELECT 24 UNION ALL
-- SELECT 25 UNION ALL SELECT 27 UNION ALL
-- SELECT 27 UNION ALL SELECT 28 UNION ALL
-- SELECT 30 UNION ALL SELECT 33
GO

--Solution:
/*
Method 1:
Reference URL - http://www.youtube.com/watch?v=Snf6Wpn-L4c&NR=1
*/


--Variable declaration
DECLARE @percentile int
DECLARE @tmp float

--Assign the PERCENTILE value which you want to calculate
Set @percentile = 25


SELECT 
@tmp = @percentile/100.0 * (COUNT(*)+1) 
FROM tblPercentile


SELECT 
CASE WHEN COUNT(ivalue) > 1 THEN SUM(ivalue)/2.0 
    ELSE MAX(iValue) 
END AS [Percentile] 
FROM tblPercentile
WHERE Sno BETWEEN FLOOR(@tmp) AND CEILING(@tmp)

/* 
Method 2:
Reference URL - http://www.thefreemathtutor.com/thepercentileformula.html
*/

SELECT iValue AS [Percentile] FROM tblPercentile
WHERE sno = (
SELECT CEILING(@percentile/100.0 * (COUNT(*))) 
FROM tblPercentile
)

Friday, September 23, 2011

Find count of each character within a word / string using SQL Server 2005+

There was a question yesterday in a forum asking how to find each character count within a word. i.e., We need to count number of occurrences of each character within the given string.

For example: If the given string is 'Amala Paul' the result should be like this:

Characters, Total Occurance
A, 4
I, 2
m, 1
P, 1
u, 1

I gave a CTE Based Solution which would work on SQL Server 2005+ to achieve this. Find below the solution which I had provided.

CTE Based Solution



DECLARE @tblCountChar TABLE 
(
strString VARCHAR(20) 
)


INSERT INTO @tblCountChar VALUES('Amala Paul')


;WITH CharacterOccurrance AS
 (
   SELECT SUBSTRING(strString, 1, 1) AS Characters,
          STUFF(strString, 1, 1, '') AS ProcessedString,
          1 AS RunningNumber
   FROM @tblCountChar
   UNION ALL
   SELECT SUBSTRING(ProcessedString, 1, 1) AS Characters,
          STUFF(ProcessedString, 1, 1, '') AS ProcessedString,
          RunningNumber + 1 AS RunningNumber
   FROM CharacterOccurrance
   WHERE LEN(ProcessedString) > 0
 )
SELECT DISTINCT(Characters), COUNT(*) AS [Total Occurance]
FROM CharacterOccurrance 
-- Add this to get rid of count for spaces :). Just remove this line and check it out
WHERE LEN(Characters)=1 
Group BY Characters
GO

Alternatively instead of SUBSTRING and STUFF we can make use of RIGHT, LEFT function with a slight change to CTE to achieve the same result.

Thursday, September 22, 2011

Script table as - ALTER TO is greyed out - SQL SERVER

One of my office colleague recently asked me why we are not able to generate ALTER Table script from SSMS. If we right click on the table and choose "Script Table As"  ALTER To option would be disabled or Greyed out. Is it a bug?



No it isn't a bug. ALTER To is there to be used for generating modified script of Stored Procedure, Functions, Views, Triggers etc., and NOT for Tables.

For generating ALTER Table script there is an work around. Right click on the table, choose "Modify" and enter into the design mode. Make what ever changes you want to make and WITHOUT saving it right click anywhere on the top half of the window (above Column properties) and choose "Generate Change Script".


Please be advised that SQL Server would drop actually create a new table with modifications, move the data from the old table into it and then drop the old table. Sounds simple but assume you have a very large table for which you want to do this! Then it would mean hell a lot of resource usage :)

Wednesday, September 21, 2011

How to add or modify IDENTITY property to an existing column in SQL Server?

Method 1: If there is no data in the table or column then use this method.

CREATE TABLE tblIdentityCheck
(
   SerialNo INT,
   FirstName VARCHAR(10)
)
GO


ALTER TABLE tblIdentityCheck DROP COLUMN SerialNo
GO

--By default, IDENTITY will start with 1 and increment by 1
Alter Table tblIdentityCheck Add SerialNo INT IDENTITY
GO

Method 2: If there is data in the table then create a new table with IDENTITY column in it. Move the data from old table to new table.

1. Create a new table with identity column
2. Enable IDENTITY_INSERT for this new table
3. Move the data from this old table to this new table.
4. Disable IDENTITY_INSERT for this new table
5. Delete the old table.
6. Rename the newtable with the oldtable name.

Let me explain the above steps with an example. Assume the table already had these records in it.


INSERT INTO tblIdentityCheck VALUES (1, 'Vadivel')
GO
INSERT INTO tblIdentityCheck VALUES (10, 'Robo')
GO

Now once we make SerialNo as an Identity column it will automatically start incrementing by 1 from the max value already present in the table. In this case it will start from 11.


BEGIN TRAN


CREATE TABLE dbo.tblIdentityCheck_Staging
(
   SerialNo int NOT NULL IDENTITY (1, 1),
   FirstName varchar(10) NULL
)
GO
SET IDENTITY_INSERT dbo.tblIdentityCheck_Staging ON
GO


INSERT INTO dbo.tblIdentityCheck_Staging (SerialNo, FirstName)
SELECT SerialNo, FirstName FROM dbo.tblIdentityCheck WITH (HOLDLOCK TABLOCKX)
GO


SET IDENTITY_INSERT dbo.tblIdentityCheck_Staging OFF
GO
DROP TABLE dbo.tblIdentityCheck
GO
EXEC sp_rename N'dbo.tblIdentityCheck_Staging', N'tblIdentityCheck', 'OBJECT'
GO
COMMIT TRAN

Method 3: Easiest option of the lot :) Change the property in the SSMS

1. Goto SQL Server Management Studio
2. Right click on that table name
3. Choose Design/Modify
4. Choose the column which needs to be set as Identity
5. At the bottom window (Column Properties) for that column you can see "Identity Specification" has been by default set to "NO".
6. Just change it to "YES" and save the table (CTRL + S)

This is easier but internally SQL Server does exactly the same process what we have seen in Method 2.


ISNULL would take the datatype and length of the parameter

In continuation to my earlier post about ISNULL and COALESCE I thought would write one of the basic difference between those two.

DECLARE @strTest VARCHAR(10)
SET @strTest = NULL

--it would return only 'Enter some'
SELECT ISNULL(@strTest, 'Enter some value')

In this case, ISNULL would return only any output which is lesser or equal to 10 characters because the variable used as its first parameter is defined as VARCHAR(10)

--it would return the complete string!
SELECT COALESCE(@strTest, 'Enter some value')

Tuesday, September 20, 2011

UPSERT (Insert and Update) in SQL Server 2005


In SQL Server 2008 using MERGE concept we can achieve INSERT and UPDATE in a single statement.

Many times we would have faced a scenario like if the key is present in the table then update that record. If the key is not present in the table then insert that as a new record. To do this we normally have to SELECT the record and see whether it EXISTS or not to decide whether to INSERT or UPDATE. The better way which I learnt recently is to make use of @@ROWCOUNT and do this with ease.

Table Structure:

CREATE TABLE tblUpsert
(
EmpID INT PRIMARY KEY,
firstname VARCHAR(25),
lastname VARCHAR(25),
emailid VARCHAR(50)
)
GO

Normal method:

/*
  This SP would work unless it isn't used in multiple threads simultaneously :)
  Same SP is used to INSERT as well as UPDATE a table.
*/
CREATE PROCEDURE uspUPSert
(
@empID INT,
@fname VARCHAR(25),
@lname VARCHAR(25),
@emailid VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON


IF EXISTS (SELECT 1 FROM tblUpsert WHERE EmpID = @empID)
BEGIN
  UPDATE tblUpsert
SET emailid = @emailid ,
firstname = @fname ,
lastname = @lname
  WHERE EmpID = @empID
END
ELSE
BEGIN
  INSERT INTO tblUpsert VALUES (@empID, @fname, @lname, @emailid)
END
END
GO

--Testing
EXEC uspUPSert 1, 'Amala','Paul', 'Amala@abc.com'
GO
EXEC uspUPSert 1, 'Amala','Paul', 'AmalaPaul@kollywood.com'
GO
EXEC uspUPSert 2, 'Sachin','Tendulkar', 'Sachin@india.com'
GO
SELECT * FROM tblUpsert
GO

Better or Alternate method to do UPSERT:

/*
Same SP is used to INSERT as well as UPDATE a table.
Here we are avoid unnecessary checking of whether the record exists or not.
Instead try to Update directly. If there is no record then @@RowCount would be 0.
Based on that Insert it as a new record.
*/
CREATE PROCEDURE uspUPSert_Ver2
(
@empID INT,
@fname VARCHAR(25),
@lname VARCHAR(25),
@emailid VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
UPDATE tblUpsert WITH (SERIALIZABLE)
SET emailid = @emailid ,
firstname = @fname ,
lastname = @lname
WHERE EmpID = @empID

IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tblUpsert VALUES (@empID, @fname, @lname, @emailid)
END
COMMIT TRAN
END
GO

--Testing
EXEC uspUPSert_Ver2 1, 'Amala','Paul', 'Amala@abc.com'
GO
EXEC uspUPSert_Ver2 1, 'Amala','Paul', 'AmalaPaul@kollywood.com'
GO
EXEC uspUPSert_Ver2 2, 'Sachin','Tendulkar', 'Sachin@india.com'
GO
SELECT * FROM tblUpsert
GO

Tuesday, September 13, 2011

How to convert Roman numerals to Integer in C#

I was looking up the web all day but couldn't find any C# code for converting Roman letters or numerals to Integer value. The best thing i came across was this code by andyb but the problem was it has been written in PHP which I had no clue :)

So I had just taken the logic of AndyB and converted it into C# for anybody who cares for it.

Add this Namespace:

using System.Collections.Generic;

Solution:

Dictionary RomanNumbers =   new Dictionary();
RomanNumbers.Add("M", 1000);
RomanNumbers.Add("CM", 900);
RomanNumbers.Add("D", 500);
RomanNumbers.Add("CD", 400);
RomanNumbers.Add("C", 100);
RomanNumbers.Add("XC", 90);
RomanNumbers.Add("L", 50);
RomanNumbers.Add("XL", 40);
RomanNumbers.Add("X", 10);
RomanNumbers.Add("IX", 9);
RomanNumbers.Add("V", 5);
RomanNumbers.Add("IV", 4);
RomanNumbers.Add("I", 1);


string strRomanValue = "MCMLXXVI";
int result = 0;


foreach (KeyValuePair pair in RomanNumbers )
{
    while (strRomanValue.IndexOf(pair.Key.ToString()) == 0)
     {
           result += int.Parse(pair.Value.ToString());
           strRomanValue = strRomanValue.Substring(pair.Key.ToString().Length);
     }
}
Response.Write(result.ToString());

Convert Datetime into YYYYMMDDHHMMSS format


Converting Datatime value in YYYYMMDDHHMMSS format is not that complex as it sounds :)

SELECT CONVERT(VARCHAR(10),GETDATE(),112) --Date alone
SELECT CONVERT(VARCHAR(10),GETDATE(),108) - Time alone


The only problem is in the Time portion we would get ":" separator within HH:MM:SS. So we need to just replace colon with space and concatenate both this result to get an output in the expected format.

Solution:

SELECT 
   CONVERT(VARCHAR(10),GETDATE(),112) + 
   REPLACE(CONVERT(VARCHAR(10),GETDATE(),108),':','') 
          AS [YYYYMMDDHHMMSS]

How many times a character (string) is repeated within a string

I am assuming in interviews this question "How to find the number of times a character or string is repeated within a string" seems to be asked quite often. May be that's why it is one of the popular question in forums :)

Normally people respond with a .NET based solution so i thought I would log a SQL Server based solution for this questions.


--Variable Declaration
DECLARE @strColumn VARCHAR(20)
DECLARE @searchString  VARCHAR(10)
DECLARE @intLen INT

--Variable Initialization
SET @strColumn = '123asd123asd123' 
SET @searchString ='123'
SET @intLen = LEN(@searchString)

--Solution
SELECT 
(
LEN(@strColumn) - 
LEN(REPLACE(@strColumn, @searchString, ''))
)/@intLen

Monday, September 12, 2011

ISNULL, COALESCE works with both INT and VARCHAR column

There was a question saying ISNULL won't work for VARCHAR columns. I thought I would provide a very basic sample where ISNULL works with string column as well.

--Sample table with a INT and a VARCHAR column
Create table tbltest
(
intSno int,
strName Varchar(10)
)
GO

--Key-in NULL for both columns
Insert into TblTest Values (null, null)
GO

Sample queries:

Select isnull(intSno, 0), isnull(strName, 0) from tblTest
GO

SELECT COALESCE(intSno, 0), COALESCE(strName, 0) from tblTest
GO

--ISNULL on a varchar column replacing with a non numeric value
SELECT ISNULL(strName, '/0') FROM tblTest
GO

--Coalesce on a varchar column
SELECT COALESCE(strName, '/0') FROM tblTest
GO

Easiest way to export query result to Excel or CSV using SQL Server

If we want to export the result of an ad-hoc query or Stored procedure from SQL Server Management Studio without writing any scripts then check this out.

Option1::

One of the easiest option if you are using SQL Server 2005 or above is to make use of the output format specifier in SQL Server Management Studio.

Step1: Open SQL Server Management Studio.
Step2: Click on New Query
Step3: Goto Menu Query >> Query Options >> Within Results Choose Text
Step4: On the right window you can see an option called "Output Format". Select "Comma Delimited".

Step5: Next either Press Control + T (to select Results to Text option). Or goto Menu Query >> Results To >> Results to Text option.

Now you are all set. Just run your SELECT statement or SP with the SELECT statement to see the result as Comma Separated Values on the screen.

You can copy and save it as a file now.

Option2::

You don't have to change any formats for this option. You just execute your SP or SELECT statement and once you got the output on the Results Window. Just "Right click" on the result and choose "Save ResultAs" to save it as CSV file!

I had given these options along with few more options which needed scripting as an response to this forum question.

How to display Indian Rupee Symbol on a webpage

I came to know about the site webRupee from Rajesh Kumar Parbat of DNS. The site explains in detail on how to display the Indian Rupee Symbol on a webpage.

Sunday, September 11, 2011

Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted

Lets see how to avoid this error:

Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted

When would we see this error?

When we try to add a column as default value for another column.

Create table tblSalesInfo
(
custId int,
SaleDate datetime,
IntimateDate datetime default (saledate - 10)
)
Go

Saledate is a column which we are trying to use it within default value assignment for IntimateDate which is not valid.

Workaround:

Create table tblSalesInfo
(
custId int,
SaleDate datetime,
IntimateDate as (saledate - 10)
)

Testing:

Insert into tblSalesInfo (custid, saledate) values (1, getdate())
Go

Select * from tblSalesInfo
Go

How to call a Stored Procedure for each record in a table

Without using Cursors how to call a stored procedure for each record in a table? This was a question asked in a forum yesterday. I come up with an alternate WHILE loop logic which is shown below:

--Sample table to hold test input data
Create table #temp_table(id int)
GO

--Lets insert some dummy data
Insert into #temp_table values (1)
Insert into #temp_table values (2)
Insert into #temp_table values (3)
Insert into #temp_table values (4)
GO

--Procedure which would just take the input
--and print current date + that number of days
CREATE PROCEDURE dbo.printdate
(
@num INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT GETDATE() + @num as [New Date]
END
GO

--Solution
DECLARE @id INT
DECLARE @cnt INT

SET @id = 0
SELECT @cnt = MAX(id) FROM #temp_table

WHILE @id < @cnt
BEGIN
SELECT TOP 1 @id = MIN(id)
FROM #temp_table
WHERE id > @id GROUP BY id ORDER BY id
EXEC dbo.printdate @num = @id
END
GO

Wednesday, September 07, 2011

SQL Server 2005 + finding 2nd highest salary

Actually have blogged about this few years back here. But recently I ran into an issue with that script. i.e., If there are more than one person getting the same salary then the results weren't coming out properly.

Let me make use of the same table structure as shown in that post.

-- Table Structure
Create table employee
(
[Name] varchar(20),
Sal int
)
Go

-- Populate Sample Records
Insert into employee values ('Vadivel', 80000)
Insert into employee values ('Vel', 70000)
Insert into employee values ('xxx', 40000)
Insert into employee values ('Sneha', 60000)
Insert into employee values ('Amala Paul', 80000) -- Have added this newly

Ideally the result we are expecting is Vel - 70000 as the 2nd highest paid employee. But the below given solution would display 'Amala Paul' as the 2nd highest paid which is wrong.

Select top 1 [Name], Sal from
(
Select Top 2 [Name], Sal from employee order by Sal Desc
) AS A1
Order by Sal

Solution:

In order to handle duplicates I made use of DENSE_RANK() function which is available in SQL Server 2005 and above only.

SELECT [Name], Sal FROM
(
SELECT DENSE_RANK() OVER (ORDER BY Sal DESC) AS Position, [Name], Sal
FROM Employee
) A1
WHERE Position = 2

This query is so generic that you can replace 2 with any number and get that ranked employee details if its available in the table.

Sunday, September 04, 2011

Calculating total of all MM:SS

Yesterday in a forum I found a question asking for a way to calculate the total of MM:SS in SQL Server. More over that column is being maintained as a "Varchar" type!!

Below is the query i suggested as my suggestion. Please note, the below query would work as long as Minutes (MM) is lesser than 60.

--Sample table structure
CREATE TABLE SumTimeTest
(
id varchar(4),
timeValue varchar(5)
)
GO

--Test data
INSERT INTO SumTimeTest VALUES('1','30:50')
INSERT INTO SumTimeTest VALUES('1','30:25')
INSERT INTO SumTimeTest VALUES('2','20:50')
INSERT INTO SumTimeTest VALUES('3','25:20')
INSERT INTO SumTimeTest VALUES('1','35:50')
GO

Solution:

SELECT CONVERT(VARCHAR(10),
DATEADD(ss,SUM(
DATEDIFF(second,0,
DATEADD(DAY,0,CAST('00:' + timevalue AS DATETIME)))),0)
,108) AS [HH:MM:SS]
FROM sumtimetest

Saturday, September 03, 2011

Error converting data type varchar to float

One of the forum member had posted a question yesterday saying she was trying to use a CASE statement and it was throwing an error as "Error converting data type varchar to float".

Let us reproduce the scenario and see the way to fix it.

--Sample table
Create table dbo.ts_vouchers
(
Lodging_Amount float
)

--Test records
Insert into dbo.ts_vouchers values (0)
Insert into dbo.ts_vouchers values (10)
Insert into dbo.ts_vouchers values (1.1)
Insert into dbo.ts_vouchers values (0)

/*
We need to display hyphen (-) if Lodging_Amount is 0.
The below query would throw the error
*/

SELECT
CASE Lodging_Amount
WHEN 0 THEN '-'
ELSE Lodging_Amount
END AS "Lodging Amount"
From dbo.ts_vouchers

Solution:

/*
We need to convert the Float value Lodging_Amount as Varchar
before displaying it.
*/

SELECT
CASE Lodging_Amount
WHEN 0 THEN '-'
ELSE Cast(Lodging_Amount as Varchar)
END AS "Lodging Amount"
From dbo.ts_vouchers