3

This is MY procedure , Im getting from and to date in the procedure i have to check if difference between FROM and TO date is greater than 2

if the condition satisfies i have to add 2days in from days and set it as TODATE

ALTER PROCEDURE [dbo].[sp_TU_AvgStdDev_Report] 
@FromDate as Datetime,
@ToDate as Datetime,
@RecipeCode as Varchar(8000),
@Grade as Varchar(10),
@WcID as Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
IF (condition to check if @FromDate - @ToDate  > 2)
--if it satisfies
SET @ToDate to @fromDate+2Days

Please help me in figuring it out...

Santhosh Raja
  • 39
  • 1
  • 9

4 Answers4

2

You need to used date functions for this.

ALTER PROCEDURE [dbo].[sp_TU_AvgStdDev_Report] 
@FromDate as Datetime,
@ToDate as Datetime,
@RecipeCode as Varchar(8000),
@Grade as Varchar(10),
@WcID as Int
AS
BEGIN
IF (datediff(day,@FromDate,@ToDate)>2)
SET @ToDate =  DATEADD(day,2, @fromDate)
PowerStar
  • 893
  • 5
  • 15
1

Use DATEDIFF and DATEADD Function

IF DATEDIFF(D,@FromDate,@ToDate)  > 2
BEGIN  
  --if it satisfies
  SET @ToDate = DATEADD(D,2, @fromDate)
END
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
1

First remove the time part from date by converting it to date like

 convert(date,convert(varchar,@FromDate))
 convert(date,convert(varchar,@ToDate))

So then you will have only date and then you can find the difference between two date using DATEDIFF sql function

Final solution 

IF DATEDIFF(day,convert(date,convert(varchar,@FromDate)),convert(date,convert(varchar,@ToDate)))  > 2
BEGIN  
  --if it satisfies
  SET @ToDate = DATEADD(day,2, convert(date,convert(varchar,@FromDate)))
END
Anurag_Soni
  • 542
  • 2
  • 17
1

You should have to use DateDiff Use below query may be it helps you.

if( Datediff(day, Todate, FromDate) > 2)
// Your query
Sandeep Kumar
  • 1,172
  • 1
  • 9
  • 22