How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server

Sql Server, , , , , , , , , , ,

In this article we will discuss on How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server?

You may also like to read the following other popular articles on Date and Time in Sql Server:

How to add Days to DateTime in Sql Server?

We can use DATEADD() function like below to add days to DateTime in Sql Server. DATEADD() functions first parameter value can be day or dd or d all will return the same result. Below example shows how we can add two days to Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
           DATEADD(day,2,GETDATE()) 'Today + 2 Days'
SELECT GETDATE() 'Today', 
           DATEADD(dd,2,GETDATE()) 'Today + 2 Days'
SELECT GETDATE() 'Today', 
           DATEADD(d,2,GETDATE()) 'Today + 2 Days'

RESULT:
Add days to DateTime in Sql Server

Alternatively, we can add Days to Date like below in Sql Server. In the below example we are add adding 2 days to a DateTime.

SELECT GETDATE() 'Today', GETDATE() + 2 'Today + 2 Days'

RESULT:
Add days to DateTime in Sql Server 1

How to add Weeks to DateTime in Sql Server?

We can use DATEADD() function to add weeks to DateTime in Sql Server. DATEADD() functions first parameter value can be week or wk or ww, all will return the same result. Below example shows how we can add two weeks to Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
           DATEADD(week,2,GETDATE()) 'Today + 2 Weeks'
SELECT GETDATE() 'Today',
           DATEADD(wk,2,GETDATE()) 'Today + 2 weeks'
SELECT GETDATE() 'Today',
           DATEADD(ww,2,GETDATE()) 'Today + 2 Weeks'

RESULT:
Add Weeks to DateTime in Sql Server 1

How to add Months to DateTime in Sql Server?

We can use DATEADD() function like below to add Months to DateTime in Sql Server. DATEADD() functions first parameter value can be month or mm or m, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:

SELECT GETDATE() 'Today', 
           DATEADD(month,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(mm,2,GETDATE()) 'Today + 2 Months'
SELECT GETDATE() 'Today',
           DATEADD(m,2,GETDATE()) 'Today + 2 Months'

RESULT:
Add Months to DateTime in Sql Server

How to add Quarters to DateTime in Sql Server?

We can use DATEADD() function like below to add Quarters to DateTime in Sql Server. DATEADD() functions first parameter value can be quarter or qq or q, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
           DATEADD(quarter,2,GETDATE()) 'Today + 2 Quarters'
SELECT GETDATE() 'Today',
           DATEADD(qq,2,GETDATE()) 'Today + 2 Quarters'
SELECT GETDATE() 'Today',
           DATEADD(q,2,GETDATE()) 'Today + 2 Quarters'

RESULT:
Add Quarters to DateTime in Sql Server

How to add Years to DateTime in Sql Server?

We can use DATEADD() function like below to add Years to DateTime in Sql Server. DATEADD() functions first parameter value can be year or yyyy or yy, all will return the same result. Below example shows how we can add two Years to Current DateTime in Sql Server:

SELECT GETDATE() 'Today',
           DATEADD(year,2,GETDATE()) 'Today + 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yyyy,2,GETDATE()) 'Today + 2 Years'
SELECT GETDATE() 'Today',
           DATEADD(yy,2,GETDATE()) 'Today + 2 Years'

RESULT:
Add Years to DateTime in Sql Server

Also Read:

How to find whether a Table is referenced by the Foreign Key constraint defined in another Table – sql server

Sql Server, , , , , ,

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server:

ALSO READ: How to find all dependencies of a table in Sql Server?

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
 OBJECT_NAME(FK.parent_object_id) 'Referring Table', 
 FK.name 'Foreign Key', 
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
		INNER JOIN sys.foreign_key_columns AS FKC 
			ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

ALSO READ: How to find referenced/dependent objects (like Table, Function etc) of a Stored Procedure/Function in Sql Server?

Let us understand this with an example:

Below script creates a database SqlHintsDemo and adds to tables Customer and Order to it. Here foreign key constraints FK_Order_Customer defined on the Order Table CustID column refers to the CustomerId column of the Customer table.

CREATE DATABASE SqlHintsDemo
GO
USE SqlHintsDemo
GO
CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] IDENTITY (1, 1) NOT NULL 
 PRIMARY KEY  CLUSTERED) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL 
         PRIMARY KEY  CLUSTERED ,
	[CustID] [int] NOT NULL ,	
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	([CustID]) REFERENCES [dbo].[Customer] ([CustomerId]) 
) 
GO

We can use a script like below to identify whether the Customer Table is referred by any other Tables foreign key constraint:

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table', 
 OBJECT_NAME(FK.parent_object_id) 'Referring Table', FK.name 'Foreign Key', 
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
		INNER JOIN sys.foreign_key_columns AS FKC 
			ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Customer'

RESULT:
Table Referenced by Another Tables Foregin Key Constraint

How to check if a Stored Procedure exists in Sql Server

Sql Server, , , , , , , , , , ,

Many a times we come across a scenario where we need to execute some code based on whether a Stored Procedure exists or not. There are different ways of identifying the Stored Procedure existence in Sql Server, in this article will list out the commonly used approaches. Let me know which approach you use and reason for the same.

To demonstrate these different approaches let us create a sample database with a Table and a Stored Procedure by the below script:

CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
CREATE TABLE dbo.Customers (CustId INT, Name NVARCHAR(50))
GO
CREATE PROCEDURE dbo.GetCustomers(@CustId AS INT)
AS
BEGIN
	SELECT * FROM  dbo.Customers WHERE CustId = @CustId
END
GO

[ALSO READ] How to check if a Table exists in Sql Server

Approach 1: Using sys.procedures catalog view

We can write a query like below to check if a GetCustomers Stored Procedure exists in the current database in any schema.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE Name = 'GetCustomers')
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures

The above query checks the existence of the GetCustomers Stored Procedure across all the schemas in the current database. Instead of this if you want to check the existence of the Stored Procedure in a specified Schema then we can re-write the above query as below:

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures 
          WHERE object_id = OBJECT_ID(N'dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures2

If you want to check the existence of a stored procedure in a database other than the current contextual database, then we can use the script like below:

USE MASTER
GO
IF EXISTS(SELECT 1 FROM SqlHintsDemoDB.sys.procedures
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers'))
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.procedures3

Note: sys.procedures catalog view contains a row for each object of the below type:

Type Description
P SQL Stored Procedure
PC Assembly (CLR) stored-procedure
RF Replication-filter-procedure
X Extended stored procedure

If you are looking for only Sql Stored Procedure, then in sys.procedures catalog views query you can add the filter AND condition as: Type = N’P’.

[ALSO READ] How to check if a record exists in table

Approach 2: Using sys.objects catalog view

sys.procedures catalog view inherits the rows from the sys.objects catalog view, sys.objects catalog view is referred to as base view where as sys.procedures is referred to as derived view. sys.procedures will return the rows only for the stored procedures whereas sys.objects view apart from returning the rows for stored procedures, it returns rows for the objects like: tables, views etc.

We can write a script like below to check the existence of a stored procedure in the current contextual database:

IF EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N'dbo.GetCustomers')
                    AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects

If you want check the existence of a stored procedure in a database other than the contextual database then we can re-write the above query by using three part naming convention as shown below:

USE master
GO
IF EXISTS (SELECT * FROM SqlHintsDemoDB.sys.objects
 WHERE object_id=OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers')
      AND type IN ( N'P', N'PC',N'X',N'RF')) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.objects2

[ALSO READ] How to check if a Temp table exists

Approach 3: Using sys.sql_modules Catalog View

We can use the sys.sql_modules catalog view to check the existence of the Stored Procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM sys.sql_modules
   WHERE object_id =  OBJECT_ID(N'dbo.GetCustomers') 
   AND OBJECTPROPERTY(object_id, N'IsProcedure') = 1) 
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using sys.sql_modules

[ALSO READ] How to check if a Database exists

Approach 4: Using OBJECT_ID() function

We can use OBJECT_ID() function like below to check if a GetCustomers Stored procedure exists in the current database.

USE SqlHintsDemoDB
GO
IF OBJECT_ID(N'dbo.GetCustomers', N'P') IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function

Specifying the Database Name and Schema Name parts for the Stored Procedure Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the stored procedure in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that, even though the current database is MASTER database, we can check the existence of the GetCustomers stored procedure in the dbo schema in the SqlHintsDemoDB database.

USE MASTER
GO
IF OBJECT_ID(N'SqlHintsDemoDB.dbo.GetCustomers', N'P')
     IS NOT NULL
BEGIN
	PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using OBJECT_ID function2

[ALSO READ] How to check if a View exists

Approach 5: Using INFORMATION_SCHEMA.ROUTINES View

We can use the INFORMATION_SCHEMA.ROUTINES view to check the existence of the stored procedure as shown below:

USE SqlHintsDemoDB
GO
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'GetCustomers' 
        AND ROUTINE_TYPE = 'PROCEDURE') 
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT
Check Stored Procedure Existence using INFORMATION_SCHEMA.ROUTINES

Approach 6: Avoid Using sys.sysobjects System table

We should avoid using sys.sysobjects System Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per Microsoft BOL link, Microsoft is suggesting to use the catalog views sys.objects/sys.procedures/sys.sql_modules instead of sys.sysobjects system table directly to check the existence of the stored procedure.

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.sysobjects  
     WHERE id = OBJECT_ID(N'dbo.GetCustomers') AND xtype=N'P' )
BEGIN
    PRINT 'Stored Procedure Exists'
END

RESULT:
Check Stored Procedure Existence using sys.sysobjects

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a View exists
How to check if Temp table exists
How to check if a record exists in table