Category: SQL Server

declare @tb1 table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)Values(‘Poter, Harry’,1,’2011-08-30 17:00:15.090′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:20.140′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-30 17:00:24.890′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:29.983′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-31 17:00:38.983′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-31 17:00:44.967′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-01 17:00:54.950′) insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-09-01 17:01:03.590′) –insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, …

—-Last Day of Previous Month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) LastDay_PreviousMonth —-Last Day of Current Month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) LastDay_CurrentMonth —-Last Day of Next Month SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) LastDay_NextMonth ResultSet: LastDay_PreviousMonth ———————– 2007-07-31 23:59:59.000 LastDay_CurrentMonth ———————– 2007-08-31 23:59:59.000 LastDay_NextMonth ———————– 2007-09-30 23:59:59.000 If you want to find last day of month of any day specified use following …

declare @body1 nvarchar(MAX) set @body1 = N’ :: Report(1-06-2011 To 30-06-2011) :: ‘ + N’ First Name Last Name Email ‘ + N” + CAST ( ( SELECT td = FirstName, ”, td = LastName, ”, td = EmailId, ” from Users ORDER BY FirstName ASC FOR XML PATH(‘tr’), TYPE ) AS NVARCHAR(MAX) ) + …

We used to use uniqueidentifier so many times in our database, since it is one of the unique field in table, we may need to put it in dynamic SQL quite a few times but I have seen so many times that even seasoned developer don’t know how to use UniqueIdentifier in dynamic SQL, may …

ex. 1. select top 10 * from dbo.split(‘Chennai,Bangalore,Mumbai’,’,’) Link: http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Fine duplicate row SELECT ColumnName, COUNT(ColumnName) AS ColumnName FROM tbl_Employee_M GROUP BY ColumnName HAVING ( COUNT(ColumnName) > 1 ) Delete duplicate rows WITH DUPLICATE(ROW,GROUPROW,ID,FNAME,LNAME) AS ( SELECT ROW,GROUPROW= CASE WHEN ID=ID THEN (SELECT COUNT(*) FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ROW, ID,LNAME,FNAME FROM MYTABLE ) AS A WHERE A.ID=B.ID AND A.ROW<B.ROW)+1 END,ID,FNAME,LNAME FROM …

Declare @table1 table ( SalesPerson varchar(50), Product varchar(50), SalesAmount float ) insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 100) insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 50) insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Pickles’, 25) insert into @table1(SalesPerson,Product, SalesAmount) values (‘Bob’, ‘Oranges’, 300) insert into @table1(SalesPerson,Product, SalesAmount) values (‘Sue’, ‘Oranges’, 500) select * from @table1 …

exec sp_rename ‘TimeZone’, ‘TimeZone_Old’

Without Any Records select * into Temp_TimeZone from ExistingTableName where 1=2 With Records select * into Temp_TimeZone from ExistingTableName

select SUBSTRING((select ‘,’ + State From State Where State.Cid = Country.Cid For xml path(”)),2,1000) as statename, Country from Country Above query generate State name with comma seperator : result as shown below