Case Statement with Variables in a Select Statement to create Start Date and End Date using an Interval Variable

I am trying to create a SQL statement that will identify my Start Date and End Date by identifying my Interval variable.

I have the below statement, which works for the first two Interval options, but if I choose any of the following Interval options, it defaults to the @Start_Date and @End_Date variables.

DECLARE 
	@Interval NVARCHAR(20)
	, @StartDate DateTime
	, @EndDate DateTime
	, @Start_Date DateTime
	, @End_Date DateTime

SET @Interval = '3 Months (Last 3 full Months)'
SET @Start_Date = '2015-07-01'
SET @End_Date = '2015-07-31'


SELECT 
	@StartDate =
		CASE
			@Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1)
			WHEN 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			ELSE @Start_Date
		END, 

	@EndDate = 
		CASE
			@Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6))
			WHEN 'Monthly (Last full Month)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			ELSE DateAdd(dd, 1, @End_Date)
		END

SELECT @StartDate, @EndDate

August 25th, 2015 4:26pm

@Interval NVARCHAR(20)
Your @Interval is defined with a max length of 20.  If you try to compare it to a string that is longer, it will only compare it to the first 20 characters of the other string (i.e. @Interval == 'Yearly (Last 12 ful' instead of @Interval == 'Yearly (Last 12 full Months)').  Increase the length of your variable @Interval to something higher like 255 or whatever is appropriate.

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 4:43pm

I tried the following option, and got the same results

DECLARE 
	@Interval NVARCHAR(20)
	, @StartDate DateTime
	, @EndDate DateTime
	, @Start_Date DateTime
	, @End_Date DateTime

SET @Interval = '3 Months (Last 3 full Months)'
SET @Start_Date = '2015-07-01'
SET @End_Date = '2015-07-31'

SET @StartDate = 
	(SELECT	
		CASE
			WHEN @Interval = 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
			WHEN @Interval = 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1)
			WHEN @Interval = 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN @Interval = '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN @Interval = 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
			WHEN @Interval = 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			ELSE @Start_Date
		END 
	)

SET @EndDate = 
	( SELECT
		CASE
			WHEN @Interval = 'Daily (Yesterday)' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
			WHEN @Interval = 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6))
			WHEN @Interval = 'Monthly (Last full Month)' THEN dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
			WHEN @Interval = '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN @Interval = 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))) 
			WHEN @Interval = 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) 
			ELSE @End_Date
		END
	)


SELECT @StartDate AS StartDate, @EndDate AS EndDate


August 25th, 2015 4:45pm

It is your variable definition for @Interval.  You are comparing it to strings that are longer than 20 characters.  In those instances, it will only compare the first 20 characters.  I corrected your query, and it appears to work.

DECLARE
    @Interval NVARCHAR(255)
    , @StartDate DateTime
    , @EndDate DateTime
    , @Start_Date DateTime
    , @End_Date DateTime

SET @Interval = '3 Months (Last 3 full Months)'
SET @Start_Date = '2015-07-01'
SET @End_Date = '2015-07-31'

SET @StartDate =
    (SELECT    
        CASE
            WHEN @Interval = 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
            WHEN @Interval = 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1)
            WHEN @Interval = 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
            WHEN @Interval = '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
            WHEN @Interval = 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
            WHEN @Interval = 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
            ELSE @Start_Date
        END
    )

SET @EndDate =
    ( SELECT
        CASE
            WHEN @Interval = 'Daily (Yesterday)' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
            WHEN @Interval = 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6))
            WHEN @Interval = 'Monthly (Last full Month)' THEN dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
            WHEN @Interval = '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
            WHEN @Interval = 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
            WHEN @Interval = 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
            ELSE @End_Date
        END
    )


SELECT @StartDate AS StartDate, @EndDate AS EndDate

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 4:49pm

First of all, why do you need to compute @EndDate and can not just use CURRENT_TIMESTAMP? Why do you need these strange manipulations?

Took me a while to figure your problem out, I admit.

You declared your variable as nvarchar(20), you should have declared @Interval as varchar(100), at

August 25th, 2015 4:50pm

HI DXHansen,

See the following statement below:

DECLARE 
	@Interval NVARCHAR(50)
	, @StartDate DateTime
	, @EndDate DateTime
	, @Start_Date DateTime
	, @End_Date DateTime

SET @Interval = '3 Months (Last 3 full Months)'
SET @Start_Date = '2015-07-01'
SET @End_Date = '2015-07-31'



SELECT 
	@StartDate =
		CASE
			@Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1)
			WHEN 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			ELSE @Start_Date
		END, 

	@EndDate = 
		CASE
			@Interval WHEN 'Daily (Yesterday)' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6))
			WHEN 'Monthly (Last full Month)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			ELSE DateAdd(dd, 1, @End_Date)
		END


Select * from 
(
	select Data from 
	(
		SELECT 
		@StartDate as startDate,
		@EndDate as EndDate
	) as q1
	UNPIVOT
	   (Data FOR q1 IN 
		  (startDate, EndDate)
	)AS unpvt
) as q2
where 
	data >=@Start_Date and data<=@End_Date







Result

Best R

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 4:51pm

The problem is that you declare @Interval NVARCHAR(20) which is not enough for the other

'3 Months (Last 3 full Months)'

And also your end data format is not consistent. Sometime it is 2014-12-31 23:59:59.997 but sometime it is 2015-08-25 00:00:00.000

A Fan of SSIS, SSRS and SSAS


August 25th, 2015 4:54pm

DECLARE 
	@Interval NVARCHAR(50)
	, @StartDate DateTime
	, @EndDate DateTime
	, @Start_Date DateTime
	, @End_Date DateTime

SET @Interval = 'Daily (Yesterday)'
--SET @Interval = 'Weekly (Sun-Sat)'
--SET @Interval = 'Monthly (Last full Month)'
--SET @Interval = '3 Months (Last 3 full Months)'
--SET @Interval = 'Yearly (Last full Year)'
--SET @Interval = 'Yearly (Last 12 full Months)'
SET @Start_Date = '2015-07-01'
SET @End_Date = '2015-07-31'


SELECT 
	@StartDate =
		CASE @Interval 
			WHEN 'Daily (Yesterday)' THEN DATEADD(dd, - 1, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), - 1)
			WHEN 'Monthly (Last full Month)' THEN DATEADD(mm, - 1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(mm, - 3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(yy, - 1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(mm, - 12, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
			ELSE @Start_Date
		END, 

	@EndDate = 
		CASE @Interval 
			WHEN 'Daily (Yesterday)' THEN DATEADD(ms, - 3, DATEDIFF(dd, 0, GETDATE()))
			WHEN 'Weekly (Sun-Sat)' THEN DATEADD(ms, - 3, DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 6))
			WHEN 'Monthly (Last full Month)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN '3 Months (Last 3 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last full Year)' THEN DATEADD(ms, - 3, DATEADD(yy, 0, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))
			WHEN 'Yearly (Last 12 full Months)' THEN DATEADD(ms, - 3, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)))
			ELSE DateAdd(dd, 1, @End_Date)
		END

SELECT @StartDate, @EndDate

Free Windows Admin Tool Kit Click here and download it now
August 25th, 2015 5:02pm

I discovered the problem was in the number of characters I had declared in the NVARCHAR. I had it set to 20, so it was cutting off the longer Interval variable choices.
  • Marked as answer by DXHansen 9 hours 43 minutes ago
August 25th, 2015 5:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics