Datediff returning date time value and not Integer

Transact-SQL
21

this second when is an error due to copy and paste . - Apologies

22

to avoid further confusion , I will run the query now in SSMS and post again

23

this is it, the second CASE Statement is the issue . thanks for any help

CASE
WHEN [LatestBaselineDate] Is Null THEN [OriginalBaselineDate] ELSE [LatestBaselineDate]
END

AS [Milestone - Baseline Date], Milestone.[Actual-ForecastDate] AS [Milestone - Forecast Date],
--[Actual-ForecastDate],

CASE
WHEN [LatestBaselineDate] Is Null THEN Cast( [OriginalBaselineDate] as sql_variant)
ELSE
Cast( Datediff(dd,[Actual-ForecastDate],[OriginalBaselineDate])as sql_variant)
END
AS [Milestone - Variance (Days)],

	Milestone.Status AS [Milestone - Status],
24

Doesn't tell me what the issue is...or what your expected results. Please provide a reproducible test script that I can run on my system (create a temp table with appropriate columns - sample data in an insert statement - and expected results using that data).

You keep falling back to stating this is an issue with the case expression - but you still have not identified why it is an issue.

The case expression is doing exactly as you asked it to do - if the LatestBaselineDate is NULL - return the OriginalBaselineDate ELSE return the variance in days. So you either get a date or the number of days...if that is incorrect then please provide sample data and expected results.

25

please provide sample DDL and DML

use sqlteam
go

--the following is just random sample, you need to provide 
--us with real or near to real data
create table #babel(LatestBaselineDate date, 
[Actual-ForecastDate] Date, OriginalBaselineDate date)

insert into #babel
select top 10000 DATEADD(dd, column_id, getdate()), 
                 DATEADD(dd, column_id * 3, getdate()),
				 DATEADD(dd, column_id * 4, getdate())
  from sys.all_columns

and then please provide expected result for the sample data you provided

create table #expectedResult()

insert into #expectedResult
select x,y,z of expected data for the sample data #babel up top

help us help you.

26

ok thats no problem I will . I know its difficult to understand without data sometimes

thanks so much for providing this for me .

27

this issue has now been resolved and sample data not needed.

Thanks for everyone that tried to help.