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 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 247158 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;

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: 247158 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!

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: 110644 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'

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

Cookies on SQLServerCentral