My Rational Rights

calculating how many days remaining in a month or last day of month or any other scenarios: declare @numberOfDaysInMonth int; set @numberOfDaysInMonth = DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(getdate()), getdate())) - 1);

SELECT GETDATE() AS [CURRENT_DATE], CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [DATE_PART], CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [TIME_PART]

Today I got an email asking how to delete duplicate records in a table? This is very common and very interesting question. The perfect answer to this question depends on the following points: 1) Volume of the data 2) Downtime of the system 3) Dependency on the table 4) Restrictions on writing the code. I am sure every database developer deals with “Delete Duplicate Records” issue at least once in a lifetime. There are so many ways we can eliminate the duplicate data from a table. In this article I would like to explain various ways to delete the duplicate records. --Create Demo Table CREATE TABLE #Employee ( EMP_ID INT, FIRST_NAME VARCHAR(30), LAST_NAME VARCHAR(30) ) --Insert Dummy Data INSERT INTO #Employee Values(1,'Stefan','Vachev'); INSERT INTO #Employee Values(2,'Michael','Allen'); INSERT INTO #Employee Values(3,'Glenn','Jhonson'); INSERT INTO #Employee Values(4,'Ray','Muran'); INSERT INTO #Employee Values(1,'...

SET IDENTITY_INSERT YourTableName ON INSERT INTO YourTableName ( CustId , FirstName , LastName ) VALUES ( 18 , 'Paul' , 'Adams' ) GO SET IDENTITY_INSERT YourTableName OFF But we must use insert into table_name(columns) values(values) Otherwise it willnot work

If you happen to forget your SQL Server password for 'sa' account, then here's a simple query to help you reset it: GO ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] GO USE [master] GO ALTER LOGIN [sa] WITH PASSWORD=N'MyNewPassword' MUST_CHANGE GO In Case you remember your Old Password and want to change the 'sa' password, use this query: ALTER LOGIN [sa] WITH PASSWORD = N'MyNewPassword' OLD_PASSWORD = 'MyOldPassword'; GO

select @@trancount SELECT dateadd(qq,4,getdate()) select DATEDIFF(DD,GETDATE(),dateadd(qq,4,getdate())) How to query a string contains %? Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'

Generate Insert Statements For a SQL Server Table For demonstration purposes, I am using the Culture table of the AdventureWorks database. USE AdventureWorks GO DECLARE @Columns VARCHAR ( max ); SET @Columns = '[CultureID], [Name], [ModifiedDate]' DECLARE @Table VARCHAR ( max ); SET @Table = 'Production.Culture' DECLARE @SQL VARCHAR ( max ) SET @SQL = 'DECLARE @S VARCHAR(MAX) SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Columns + ')'') + CHAR(13) + CHAR(10) + ''SELECT '' + ' + REPLACE ( REPLACE ( REPLACE (@Columns, ',' , ' + '', '' + ' ), '[' , ''''''''' + CAST(' ), ']' , ' AS VARCHAR(max)) + ''''''''' ) + ' FROM ' + @Table + ' PRINT @S' EXEC (@SQL) OUTPUT

view plain copy to clipboard print ? State (Tablename) ----- City (Columnname) ----- Chennai Madurai Coimbatore declare @retstr varchar (8000) select Top 5 @retstr = COALESCE (@retstr + ';' , '' ) + City from State print @retstr

if(0='') print 'yes' else print 'no' Result is yes The result would be 'Yes'. "When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence" ( http://msdn.microsoft.com/en-us/library/ms190309.aspx books on line). This means that the text is implicitly converted to integer with a value of 0.