Scalar function for calculating date

Suppose we want to keep handy a function that will calculate and give date going backward or forward any number of days from current date. So say, we want to get a date for 100 days prior to current date we can keep this function handy.

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

create FUNCTION [dbo].[CalculateDate]

    (

        @dd int

    )

    RETURNS datetime

    AS

    BEGIN

    declare @date datetime

            SET @date = (select dateadd(dd,@dd,DATEADD(dd, DATEDIFF(dd,'',getdate()), '')))

 

            RETURN (@date)

END

 

GO

Result :

--If we want to have date for 365 days prior to current day, below will be the query for using the function written above:

select [dbo].[CalculateDate](365)

Tags: , , , , ,

Scalar Function | SQL Server | SQL Tricks