Vadivel's blog

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)

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];

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.

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 A...

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,  ...

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 woul...

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...

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')

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 FRO...

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 = "M...

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]

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

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

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 yo...

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.

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

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

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: I...

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

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