Convert Teradata CASE to t-SQL CASE with CAST and an Operator

Question

Post reply

Convert Teradata CASE to t-SQL CASE with CAST and an Operator

quinn.jay

SSCertifiable

Points: 5505
More actions
September 12, 2017 at 12:59 pm

#323500

Hello, I've got a Teradata Case statement, and I'm trying to convert it to a T-SQL Case statement, and I keep getting an invalid data type operator being used.
Here is the Teradata:
CASE
WHEN actvt_dt BETWEEN '2015-08-14' AND '2015-09-10'
THEN actvt_dt - CAST('2015-08-14' AS DATE) + 1
Here is one of the variations I've made, and I keep getting the error: Operand data type date is invalid for subtract operator.
, CASE
WHEN actvt_dt BETWEEN '2015-08-14' AND '2015-09-10'
THEN actvt_dt - cast('2015-08-14' as DATE) -+ 1
The actvt_dt is a 'date' data type in SQL Server
Any ideas on whatelse I can try?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

Like this, perhaps?

DECLARE @actvt_dt DATE = '20150815';

SELECT CASE WHEN @actvt_dt BETWEEN '20150814' AND '20150910' THEN DATEDIFF(DAY, '20150814', @actvt_dt) + 1 END;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

quinn.jay SSCertifiable Points: 5505 More actions · Answer 2

Phil Parkin - Tuesday, September 12, 2017 1:40 PM
Like this, perhaps?
DECLARE @actvt_dt DATE = '20150815';
SELECT CASE WHEN @actvt_dt BETWEEN '20150814' AND '20150910' THEN DATEDIFF(DAY, '20150814', @actvt_dt) + 1 END;

Yes, but why have to declare to hold that date value when I could just hard code that set period date?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

quinn.jay - Tuesday, September 12, 2017 1:45 PM
Phil Parkin - Tuesday, September 12, 2017 1:40 PM
Like this, perhaps?
DECLARE @actvt_dt DATE = '20150815';
SELECT CASE WHEN @actvt_dt BETWEEN '20150814' AND '20150910' THEN DATEDIFF(DAY, '20150814', @actvt_dt) + 1 END;
Yes, but why have to declare to hold that date value when I could just hard code that set period date?

I had to hard-code it to make the example self-contained and runnable. You, of course, do not! 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

quinn.jay SSCertifiable Points: 5505 More actions · Answer 4

Phil Parkin - Tuesday, September 12, 2017 1:57 PM
quinn.jay - Tuesday, September 12, 2017 1:45 PM
Phil Parkin - Tuesday, September 12, 2017 1:40 PM
Like this, perhaps?
DECLARE @actvt_dt DATE = '20150815';
SELECT CASE WHEN @actvt_dt BETWEEN '20150814' AND '20150910' THEN DATEDIFF(DAY, '20150814', @actvt_dt) + 1 END;
Yes, but why have to declare to hold that date value when I could just hard code that set period date?
I had to hard-code it to make the example self-contained and runnable. You, of course, do not! 🙂

Ok, I like using the declare, however now I'm trying to create a view, and I can't declare.

sgmunson SSC Guru Points: 110640 More actions · Answer 5

quinn.jay - Tuesday, September 12, 2017 2:32 PM
Phil Parkin - Tuesday, September 12, 2017 1:57 PM
quinn.jay - Tuesday, September 12, 2017 1:45 PM
Phil Parkin - Tuesday, September 12, 2017 1:40 PM
Like this, perhaps?
DECLARE @actvt_dt DATE = '20150815';
SELECT CASE WHEN @actvt_dt BETWEEN '20150814' AND '20150910' THEN DATEDIFF(DAY, '20150814', @actvt_dt) + 1 END;
Yes, but why have to declare to hold that date value when I could just hard code that set period date?
I had to hard-code it to make the example self-contained and runnable. You, of course, do not! 🙂
Ok, I like using the declare, however now I'm trying to create a view, and I can't declare.

That's what stored procedures are for, but without more information on why you were choosing a view, it might be better as an ITVF (in-line table-valued function), but there's nothing in your post to help make that decision. What's your overall objective?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

AndersBendix SSC Rookie Points: 34 More actions · Answer 6

The suggested code is correct. When performing atrihmetics on DATE in Teradata - in this case subtracting the dates from each other - the result will be the difference expressed in number of days.
Teradata doesn't do the same thing when subtracting or adding an integer to/from a DATE (assuming INTERVAL DAY). Here the result is a new DATE.

So the clostes thing in SQL Server is DATEDIFF as correctly proposed by Phil.

Maybe this example makes it easier to understand

DECLARE @actvt_dt TABLE
(actvt_dt DATE)

INSERT INTO @actvt_dt
VALUES ('2015-01-01'),
('2015-08-14'),
('2015-08-16'),
('2015-09-01'),
('2015-10-14')

SELECT
CASE
WHEN actvt_dt BETWEEN '2015-08-14' AND '2015-09-10'
THEN DATEDIFF(DAY, CAST('2015-08-14' AS DATE), actvt_dt) + 1
END
FROM @actvt_dt

jimpan Grasshopper Points: 23 More actions · Answer 7

From TeraData SQL assist, you get 7 as result from the query.

In TSQL, you may able to do the following
declare @actvt_dt date = '2015-08-22'
SELECT CASE WHEN @actvt_dt BETWEEN '2015-08-14' AND '2015-09-10'
THEN ABS(DATEDIFF(DAY,@actvt_dt,DATEADD(DAY,1, '2015-08-14')))
END AS 'test'