–For MS SQL Server 2012 and above USE [master]; DECLARE @kill varchar(8000) = ”; SELECT @kill = @kill + ‘kill ‘ + CONVERT(varchar(5), session_id) + ‘;’ FROM sys.dm_exec_sessions WHERE database_id = db_id(‘MyDB’) EXEC(@kill); –For MS SQL Server 2000, 2005, 2008 USE master; DECLARE @kill varchar(8000); SET @kill = ”; SELECT @kill = @kill + ‘kill…
SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END – dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME,…
SELECT DB_NAME(dbid) AS [Database Name], COUNT(dbid) AS [No Of Connections], loginame AS [Login Name] FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame;
— High VLF counts can affect write performance — and they can make full database restores and crash recovery take much longer — Try to keep your VLF counts under 200 in most cases (depending on log file size) CREATE TABLE #VLFInfo ( FileID INT, FileSize BIGINT, StartOffset BIGINT, FSeqNo BIGINT, [Status] BIGINT, Parity BIGINT,…
declare @cmd nvarchar(200) set @cmd = ‘move \\server\UserDB\*.bak \\server\UserDB\Archive’ exec xp_cmdshell @cmd
DECLARE @query VARCHAR(1000), @dbname VARCHAR(1000), @count INT; SET NOCOUNT ON; DECLARE csr CURSOR FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases; CREATE TABLE ##loginfo ( dbname VARCHAR(100), num_of_rows INT ); OPEN csr; FETCH NEXT FROM csr INTO @dbname; WHILE(@@fetch_status -1) BEGIN CREATE TABLE #log_info ( RecoveryUnitId TINYINT, fileid TINYINT, file_size BIGINT, start_offset BIGINT, FSeqNo INT, [status]…
Error message when you open SQL Server Configuration Manager in SQL Server: “Cannot connect to WMI provider. You do not have permission or the server is unreachable” https://support.microsoft.com/en-us/kb/956013
Static Tables – Set Fill Factor at 100 (or default server fill factor), As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation. Tables Updated Less Often – Set Fill Factor at 95. These tables are more or less…
— 25 missing indexes that will improve performance the most SELECT TOP 25 dm_mid.database_id AS DatabaseID, DB_NAME(dm_mid.database_id) AS [Database], dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact], dm_migs.last_user_seek AS [Last Usage Time], OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table], ‘CREATE INDEX [IDX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”),…
SELECT( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE(( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE( cpu_count / hyperthread_ratio ) * (( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS TotalNumberOfCores,…
Classic way to check the database is alright. DBCC CHECKDB(‘DatabaseName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction. For example, if SPID is 69 then execute the command as sp_who2 69 sp_lock 69 Now , you can…
SELECT SJ.name as job_name , SJ.enabled as is_job_enabled , SS.enabled as is_schedule_enabled /*, ISNULL(SJ.description, ”) as job_desc*/ , SS.name as schedule_name , CASE freq_type WHEN 1 THEN ‘Occurs on ‘ + STUFF(RIGHT(active_start_date, 4), 3,0, ‘/’) + ‘/’ + LEFT(active_start_date, 4) + ‘ at ‘ + REPLACE( RIGHT(CONVERT(varchar(30), CAST(convert(varchar(8), STUFF(STUFF(RIGHT(‘000000’ + CAST(active_start_time as varchar(10)), 6), 3,…
– Unused Index Script SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , ‘DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’…
SELECT DISTINCT wt.wait_type FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id WHERE s.is_user_process = 0
DECLARE @CounterPrefix NVARCHAR(30) SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’ THEN ‘SQLServer:’ ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’ END ; – Capture the first counter set SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] , counter_name , instance_name , cntr_value , cntr_type , CURRENT_TIMESTAMP AS collection_time INTO #perf_counters_init FROM sys.dm_os_performance_counters WHERE ( OBJECT_NAME = @CounterPrefix + ‘Access Methods’ AND counter_name = ‘Full…
SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset , sql_handle , plan_handle , total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY avg_physical_reads DESC
SELECT TOP 10 wait_type , max_wait_time_ms wait_time_ms , signal_wait_time_ms , wait_time_ms – signal_wait_time_ms AS resource_wait_time_ms , 100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits , 100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits , 100.0 * ( wait_time_ms – signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits FROM sys.dm_os_wait_stats WHERE…
To back up the tail of the transaction log 1 After connecting to the appropriate instance of the Microsoft SQL Server Database Engine, in Object Explorer, click the server name to expand the server tree. 2 Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database….
Types of Replication Microsoft SQL Server provides the following types of replication for use in distributed applications: Transactional replication. For more information, see Transactional Replication. Merge replication. For more information, see Merge Replication. Snapshot replication. For more information, see Snapshot Replication.
Index type Description Clustered A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Non clustered A non clustered…
Database Engine Stored Procedures (Transact-SQL) SQL Server 2014 QL Server supports the following system stored procedures that are used for general maintenance of an instance of SQL Server. sp_add_data_file_recover_suspect_db sp_executesql sp_add_log_file_recover_suspect_db sp_getapplock sp_addextendedproc sp_getbindtoken sp_addextendedproperty sp_help sp_addmessage sp_helpconstraint sp_addtype sp_helpdb sp_addumpdevice sp_helpdevice sp_altermessage sp_helpextendedproc sp_attach_db sp_helpfile sp_attach_single_file_db sp_helpfilegroup sp_autostats sp_helpindex sp_bindefault sp_helplanguage sp_bindrule sp_helpserver sp_bindsession…
READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would…
CONCAT Function Prior to SQL Server 2012, we used to use the “+” operator to combine or concatenate two or more string values but starting with SQL Server 2012, we can use CONCAT function to perform this type of operation more neatly. For example, the below script and screenshot shows usage of the “+” operator…
STUFF Function We looked at using the CONCAT function to concatenate or combine two or more string values. With this function, string values are appended at the end of the prior string but there are times when you want to insert a string value into another string value or replace a part of one string…
To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined: WITH CTE_1 AS ( SELECT db_name() as dbname, o.name as tablename, (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id AND i.object_id = p.object_id) as number_of_rows FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id =…
These three issues are all related, yet very different. When we deal with reports of problems, it’s essential to clearly identify what is actually happening. Sometimes, we need clarification by asking about the symptoms, instead of accepting what the user says without delving any deeper. The root cause of a problem may not even be…
SET XACT_ABORT is helpful in many ways. example: suppose you are working on a stored procedure which is doing a complex data manipulation. You though this query will run with in 1 minute when you run it from .NET or other code , but some how it took quite long time and with in one…
Locking hint Description HOLDLOCK Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE. NOLOCK Do not issue shared locks and do not honor exclusive locks. When this option is in effect,…
Errors 823,824, and 825 To do this, run the following command: USE msdb GO EXEC msdb.dbo.sp_add_alert @name = N’823 – Read/Write Failure’, @message_id = 823, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 1 GO EXEC msdb.dbo.sp_add_alert @name = N’824 – Page Error’, @message_id = 824, @severity = 0, @enabled =…
DBCC DBREINDEX This statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation. DBCC DBREPAIR This statement is used to drop or delete a damaged database. However, this command is no longer available with Microsoft…
As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as DECLARE @Counter bigint SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Transactions/sec’ AND object_name=‘SQLServer:Databases’ AND instance_name =‘Test’ – Your DB Name – Wait for…
DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_function] @pLayout=@Layoutfull,@pValues=@Data SELECT @ret AS [result]
MS SQL 2005+ SELECT @@servername AS instance_name ,a.name AS database_name, c.data_Size_MBs AS data_size , b.log_Size_MBs AS log_size ,a.total_Size_MBs AS total_size FROM ( SELECT d.name, ROUND(SUM(mf.size) * 8 / 1024, 0) total_Size_MBs FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 — Skip system databases GROUP BY d.name ) AS…
SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Table] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article];
SELECT @@SERVERNAME AS server_name, sjs.last_run_outcome,sj.enabled, sj.name, sjs.step_id, sjs.step_name, sjs.database_name, CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) AS failed_date FROM msdb.dbo.sysjobsteps AS sjs INNER JOIN msdb.dbo.sysjobs AS sj ON sjs.job_id = sj.job_id WHERE (sjs.last_run_date <> 0) AND (CAST(CAST(sjs.last_run_date AS varchar(10)) AS datetime) > GETDATE() – 2) ORDER BY sj.name
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2),r.percent_complete)AS [Percent Complete], CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL Command] FROM sys.dm_exec_requests AS r WHERE r.command IN (‘RESTORE DATABASE’,’BACKUP DATABASE’)
select pvt.abc,pvt.def,pvt.ghi from ( SELECT ConnectionName, Connection.ConnectionString FROM dbo.Global_Applications_ConnectionString AS Connection ) AS SourceTable pivot ( min(ConnectionString) for ConnectionName in (abc,def,ghi) ) as pvt
Visual studio: How to remove projects from the recent projects list. Open the registry by clicking Start (Start > Run) and type RegEdit > (enter) The Registry Editor will open. Drill down the following path… HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\ProjectMRUList… This is where you recent projects are stored in Visual Studio 2008. (HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList for Visual Studio 2008).
select article, * from dbo.MSreplication_objects where article like ‘Table_name’ select sp.name, ss.srvname, sa.name from sysarticles sa join syssubscriptions ss on ss.artid = sa.artid join syspublications sp on sa.pubid = sp.pubid where sa.name like ‘table_name’
SELECT DB_NAME() AS [Database], T.TABLE_NAME AS [Tables without Primary Keys] FROM INFORMATION_SCHEMA.TABLES AS T WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = TC.TABLE_NAME) AND T.TABLE_TYPE = ‘BASE TABLE’
USE master; GO — Create the Event Session IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’DBDeletedCreated’) DROP EVENT SESSION DBDeletedCreated ON SERVER; GO CREATE EVENT SESSION DBDeletedCreated ON SERVER ADD EVENT sqlserver.object_created ( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ ), ADD EVENT sqlserver.object_deleted( SET collect_database_name = (1) ACTION(sqlserver.nt_username,sqlserver.session_id,sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.sql_text) WHERE object_type = ‘DATABASE’ )…
SELECT CustomerID , SalesOrderID , CAST(OrderDate AS DATE) AS OrderDate , TotalDue , LAST_VALUE(TotalDue) OVER ( PARTITION BY CustomerID ORDER BY SalesOrderID ) AS TD FROM Sales.SalesOrderHeader ORDER BY CustomerID
CREATE PROCEDURE dbo.ParseXMLtoTable @strXML AS XML ,@rootnode NVARCHAR(255) AS BEGIN SET NOCOUNT ON DECLARE @strText AS NVARCHAR(MAX) ,@idoc INT ,@id INT ,@parentid INT IF OBJECT_ID(‘tempdb..#ChildList’) IS NOT NULL DROP TABLE #ChildList CREATE TABLE #ChildList ( [RowNum] INT IDENTITY(1,1) NOT NULL, [parentid] INT NULL, [id] INT NULL, PRIMARY KEY (RowNum)) IF OBJECT_ID(‘tempdb..#NodeList’) IS NOT NULL DROP…
IF EXISTS (SELECT * FROM sys.objects WHERE type = ‘P’ AND name = ‘Usp_findstring’) DROP PROCEDURE Usp_findstring GO CREATE PROC Usp_findstring (@INPUTSTRING VARCHAR(max), @SEARCHSTRING VARCHAR(500)) AS BEGIN DECLARE @OCCURENCES AS INT, @POSITION AS INT, @RETURNVALUE INT SET @OCCURENCES = 0 SET @POSITION = 0 WHILE @POSITION < Len(@INPUTSTRING) BEGIN IF Charindex(@SEARCHSTRING, @INPUTSTRING, @POSITION) > 0…
USE [Leads] GO SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE PROCEDURE [dbo].[sp_PingBLSWebsites] AS DECLARE @counter int DECLARE @result VARCHAR(15) DECLARE @ip varchar(15) DECLARE @web varchar(100) DECLARE @webs varchar(100) set @counter = 0 — ping all the IP’s WHILE @counter <(SELECT COUNT(*)AS blscount FROM dbo.keywords_domains where URLip isnull) BEGIN SELECT top 1 @web=replace([url],’http://’,”),@webs=replace([url],’http://’,”) FROM keywords_domains WHERE…
— “DT” is an abbreviation for “DATETIME”, not “DATE” DECLARE @StartDT DATETIME ,@EndDT DATETIME ; SELECT @StartDT = ‘2000-01-01 10:30:50.780’ ,@EndDT = ‘2000-01-02 12:34:56.789’ ; –===== Display the dates and the duration SELECT StartDT = @StartDT ,EndDT = @EndDT ,Duration = @EndDT-@StartDT ;
USE [leads] GO /****** Object: StoredProcedure [dbo].[sp_get_ip_address] Script Date: 02/22/2013 17:08:45 ******/ SET ANSI_NULLSON GO SET QUOTED_IDENTIFIERON GO CREATE Procedure [dbo].[sp_get_ip_address](@web varchar(100), @ip varchar(15) out) as begin Declare @ipLine varchar(200) Declare @pos int DECLARE @strCmd VARCHAR(60) DECLARE @strServerIP VARCHAR(100) DECLARE @result VARCHAR(15) set nocounton set @ip =null set @strServerIP =@web SELECT @strCmd =‘ping ‘+ @strServerIP Insert…
How to Fix SQL Server Database Error 3707? 1. ALTER Database leads SET Emergency; GO 2. ALTER Database leads Set Single_User GO 3. DBCC CHECKDB (leads, REPAIR_ALLOW_DATA_LOSS) With ALL_ERRORMSGS; GO 4. ALTER Database leads set multi_user GO
EXEC @result=Master..xp_cmdShell @strCmd
backup log Leads with TRUNCATE_ONLY dbcc shrinkdatabase (Leads , TRUNCATEONLY)
DBCC SHRINKDATABASE (N’Leads’, 0,TRUNCATEONLY)
backup.sql …. BACKUP DATABASE [AdaptiveCR] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdaptiveCR.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdaptiveCR-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO MF20bak.cmd…. sqlcmd -S ICOWK077\AMCR -i C:\backup.sql Scheduled Tasks to call MF20bak.cmd
(16777216 * CONVERT(float, PARSENAME(URLip, 4))
PARSENAME(URLip, 4)
— Declare variables to use DECLARE @x int DECLARE @Price money DECLARE @LENGTH int DECLARE @prefix1 varchar(100) DECLARE @Match varchar (100) — Test for supplied value SET @Price = (select price from table1 where breakout_no LIKE @prefix ) — Was it found? If so, then print it and skip the rest of the code IF…
EXEC sp_configure ‘xp_cmdshell’, 1
How to output a file from a stored procedures in MS SQL, this is how I did it a few years ago exec master..xp_cmdShell ‘bcp “SELECT url.loc, url.publication_date, url.keywords FROM ICONews..vw_googlenews url For XML Auto,Elements” queryout C:InetpubwwwrootwebsiteSQLgoogle.xml -c -t, -T -S IP address’ Link: http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell
HAVING (COUNT(*) > 1)
(PATINDEX(‘%.%’, REPLACE(REPLACE(url, ‘www.’, ”), RIGHT(url, 7), ”)) > 1)
{ fn LCASE((CASE WHEN CHARINDEX(‘/’, url) = 0 THEN ‘http://www.’ + REPLACE(REPLACE(url, ‘www3.’, ”), ‘www.’, ”)
ELSE CASE WHEN (url) LIKE ‘www’ THEN ” ELSE ‘http://www.’ + REPLACE(REPLACE(REPLACE(LEFT(url, CHARINDEX(‘/’, url)), ‘www3.’, ”), ‘www.’, ”), ‘/’,
”) END END)) } AS websiteurl
CODE to: connect to Ms SQL, search google, and scrape sponsored links.
DECLARE @enteredOn datetime; — Sun IF datepart(dw, getdate()) = 1 Set @enteredOn =getdate()-7 (CONVERT(datetime, CONVERT(varchar, SOEB.ASK_salesproccomp, 103), 103) >= CONVERT(datetime, CONVERT(varchar, @enteredOn, 103), 103)) Software engineers do the job better!
DECLARE @Datafull XML DECLARE @Layoutfull XML SET @Datafull = dbo.ufn_Common_Layout_DataCells_AsXML(@Layoutfull, 24125, 1, 0, 0) SET @Layoutfull = dbo.ufn_Common_Layout_ByDealerAndItemFromCache_AsXML_ex(5,5030, 658) select @Datafull AS [values] select @Layoutfull AS [layout] DECLARE @ret XML; EXEC @ret = [ufn_Common_Layout_InsertValues] @pLayout=@Layoutfull,@pValues=@Datafull select @ret AS [result]
CASE WHEN DATEDIFF(mm, NPB.CreatedOn, soeb.ASK_ContractEndDate) > 1 THEN DATEDIFF(mm,NPB.CreatedOn, soeb.ASK_ContractEndDate) ELSE 0 END Software engineers do the job better!
MS SQL To find the start of the month This code: DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) was excuted on 2/3/2010 at 16:11 the result: 01/03/2010 00:00:00 Software engineers do the job better!