Datediff returning date time value and not Integer

Transact-SQL
1

Hello

the below is only part of the main query .this is the bit am having a issue with . Can someone please help . the value returned in the Milestone Variance days is date time rather than the No of days in INT
not sure why this is happening as I would like to the results in INTEGER

Latestbaseline column ---------datetime
Originalbaseline ------------------datetime
Actual-ForecastDate-------------datetime

SELECT CASE
WHEN CAST [LatestBaselineDate] as INT) is NULL THEN CAST [OriginalBaselineDate] as INT) ELSE

 Datediff(day,[Actual-ForecastDate],[OriginalBaselineDate])

END
AS [Milestone - Variance (Days)]

2

looks like syntax issues. Missing Open paren after Cast

SELECT CASE
WHEN CAST ( [LatestBaselineDate] as INT) is NULL THEN CAST ([OriginalBaselineDate] as INT) ELSE

3

While your query is syntactically correct, the logic seems unusual. You can see this if you run the following two queries:

SELECT CAST(GETDATE() AS INT);
SELECT DATEDIFF(DAY,'20201101',GETDATE());

So the if the case expression evaluates the WHEN part, you would get a number like 44166 (which is the number of days since 1/1/1900, but the ELSE part, you would get a small number such as 32 in my example. Maybe, that is what you intended, but that logic seems not very intuitive.

4

Hi JamesK

maybe am not explaining myself .

the query was initially

SELECT CASE
WHEN [LatestBaselineDate] is NULL THEN [OriginalBaselineDate] as ELSE

 Datediff(day,[Actual-ForecastDate],[OriginalBaselineDate])

END
AS [Milestone - Variance (Days)]

and I was getting the following result which is not what I want

Milestone -Variance(Days)
2011-05-04 00:00:00:000

meanwhile if I do a SELECT datediff(day,[Actual-ForecastDate],OriginalBaselineDate] FROM Milestones I get the actual no of days

No Column Name

-212
0
18

so I googled and read that you cant have Case statements with different data types hence the reason I did a CAST as INT

but even after this I was getting things like

Milestone -Variance (Days)
40795

still not what I want .

5

oh now Yes I understand what you are saying but so how do I make the query return No of days

since according to you the query is not evaluating the ELSE Part .

so when Latestbaseline is NOT NULL , I am getting the funny 40975 numbers in the Variance (days) column

I hope have managed to explain what the issue is.

6

Who says you can't have different types?! But you have to "tell" SQL that's what you want do!

SELECT CASE
WHEN [LatestBaselineDate] is NULL THEN CAST([OriginalBaselineDate] AS sql_variant) ELSE
    CAST(Datediff(day,[Actual-ForecastDate],[OriginalBaselineDate]) AS sql_variant)
END
AS [Milestone - Variance (Days)]
7

Maybe you can clarify what your expected results should be?

Based on your logic - you seem to want the variance in days between 2 dates. The ELSE statement gets you that value using DATEDIFF. Now...if the LatestBaselineDate is NULL...what is the expected value and how would it be calculated? If the LatestBaselineDate is NOT NULL...what is the expected value and how would it be calculated?

As a guess...I am thinking you want to calculate the variance from the LatestBaselineDate if it exists, else use the OriginalBaselineDate - if so:

SELECT [Milestone - Variance (Days)] = datediff(day, [Actual-ForecastDate], coalesce(LatestBaselineDate, OriginalBaselineDate))

This will use the LatestBaselineDate if it is not null - else it uses the OriginalBaselineDate and calculates your variance in days.

8

Thanks so much Jeffw8713. you seem to understand what am trying to achieve but your query Milestone Variance (days) is still returning datetime values

this is what am trying calculate

if LatestBaselineDate is Null THEN return OriginalBaselineDate

if LatestBaseline IS NOT NULL, then Calculate the difference in days(I want the column name to be called Milestone-Variance (days) between

(Actual-Forecast -OriginalBaselineDate]

thats All .

this is the query I ran as per your suggestion- dont think we need Coalesce

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

--(SELECT [Milestone - Variance (Days)] = Datediff(dd,[Actual-ForecastDate],coalesce([LatestBaselineDate], [OriginalBaselineDate])))

END
AS [Milestone - Variance (Days)],

table format as shown sample

OriginalbaselineDate Actual-Forecast. Milestone-Variance (days)

NULL. 2019-12-03:00:00:00:000.

2020-11-01:00:00:00:000 2025-11-02:00:00:00:000. -212

Thanks a lot

9

You cannot return a date - or a difference - in the same column. Your CASE expression is going to return a date because the first condition returns OriginalBaselineDate. The second expression therefore will also be a date - and SQL Server will convert an integer value to its corresponding datetime value.

If you do this SELECT CAST(0 AS datetime) it will return a date of '1900-01-01 00:00:00.000' and if you do this SELECT CAST('' AS datetime) then SQL Server implicitly converts '' to a 0 which is then converted to a datetime value.

The DATEDIFF function is not returning a datetime value - it is returning the difference in days...but because you have your case expression returning a datetime...it is converted.

Going back to what you are trying to accomplish...it seems you want either a datetime value (Milestone?) or the Variance (Days)? in a single column? If that is the case - then you need to CAST each part of the case expression to a common data type.

There are 2 ways to do this:

SELECT CASE WHEN LatestBaselineDate IS NULL THEN CAST(OriginalBaselineDate AS sql_variant)
ELSE CAST(datediff(day, [Actual-ForecastDate], OriginalBaselineDate) AS sql_variant)
END

Or - you can cast/convert to varchar(23):

SELECT CASE WHEN LatestBaselineDate IS NULL THEN CONVERT(varchar(23), OriginalBaselineDate, 121)
ELSE CONVERT(varchar(23), datediff(day, [Actual-ForecastDate], OriginalBaselineDate))
END

The problem with this approach is that you are mixing data types and values.

10

Hmm, I guess you didn't see my example where I did return a date and a difference in the same column :grinning:.

11

Well - you didn't...you returned a sql_variant and I was just pointing out to the OP (again) that he cannot do what he is attempting to do with using CAST or CONVERT.

12

Thanks Jeff so much for your explanation regarding the datediff function is actually returning INT but because of my first CASE expression thats returning datetime then SQL converts it to datetime. I now understand but also am not trying to return a difference in date from same Column, these are 2 different columns here . just giving an Alias to the columns thats all.

anyway am using your suggested approach No 1.

the funny thing is am now getting Variance days as INT for some rows but not ALL

Please find Sample result below

Milestone BaselineDate Milestone-Forecast Date. Milestone Variance(Days). Expected

2011-06-01:00:00:00:000. 2010-06-01:00:00:000. 2010-06-01:00:00:000. 0
2011-06-30:00:00:00:000. 2010-06-30:00:00:000. 2010-06-30:00:00:000. 0
2010-10:01:00:00:00:000. 2010-10-01:00:00:000. 0. correct

13

Can you provide the code you are using now? Also - how many columns are you trying to return here, it really isn't clear what you are expecting to happen.

It would be much easier if you could put together a set of sample data and expected output. For example, either create a temp table or declare a table variable - populate it with sample data using an insert statement - and identify the expected results.

My first suggested approach was to use coalesce to calculate the variance based on either the LatestBaselineDate or the OriginalBaselineDate, the second option is the same as Scott's - which is to CAST the return value as a sql_variant data type - where you get either the OriginalBaselineDate or the days variance.

Do you want the days variance - or do you want a date?

14

Hello

apologies but will try and be as detailed as possible .

  1. I have 3 Columns to return

MilestoneBaselineDate
MilestoneForecastDate
Milestone Variance (Days)

  1. I need Milestone Variance (Days) to return INT

  2. the sql variant code you provided is what I am now using and as I said earlier , its now giving me the result I want in days (datatype INT) but NOT for all rows

  3. as you can see from the 4 rows I have provided I expect 0days in the last Column (Milestone Variance )

(My Apologies , the original above was wrong thats why you couldn't understand .

1.as you can see below the Baseline dates and Actual Forecast are same dates so a subtraction should give zero for all rows

  1. the last row also should be zero but giving -212 . this is wrong

Baseline. Forecast Variance in Days
2011-06-01:00:00:00:000. 2011-06-01:00:00:000. 2010-06-01:00:00:000.
2011-06-30:00:00:00:000. 2011-06-30:00:00:000. 2010-06-30:00:00:000.
2010-10-01:00:00:00:000. 2010-10-01:00:00:000. 0
2012-03-31:00:00:00:000. 2012-03-31:00:00:000. -212

here's the Code am using now. putting all the code here . ive outlined the part am having the issue with . other than that the main query runs OK .

SELECT
[vwProject - Last QTR].ProjReturnID,
[vwProject - Last QTR].ProjectID,
[vwProject - Last QTR].[ID Number],
Key_Match.IDQ, Key_Match.[Qtr Name] AS Quarter,
[vwProject - Last QTR].Department,
[vwProject - Last QTR].ProjectName AS [Project Name - Most Recent],
Milestone.MilestoneDesc AS [Milestone - Name],
vwMilestoneTypeNewOld.CurrentName AS [Milestone - Type],
Milestone.[Version No] AS [Milestone - Version Number],
--IIf([LatestBaselineDate] Is Null,[OriginalBaselineDate],[LatestBaselineDate]) AS [Milestone - Baseline Date],
--Milestone.[Actual-ForecastDate] AS [Milestone - Forecast Date], [Actual-ForecastDate]-
--(IIf([LatestBaselineDate] Is Null,[OriginalBaselineDate],[LatestBaselineDate])) AS [Milestone - Variance (Days)],
--Milestone.Status AS [Milestone - Status], Milestone.MilestoneNotes AS [Milestone - Comments], Milestone.Source AS [Milestone - Category], Milestone.[Critical Path] AS [Delivery Critical Path]

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],

Milestone.MilestoneNotes AS [Milestone - Comments],
Milestone.Source AS [Milestone - Category], Milestone.[Critical Path] AS [Delivery Critical Path]

FROM
((Milestone
INNER JOIN Key_Match ON Milestone.ProjReturnID = Key_Match.ProjDetID)
INNER JOIN [vwProject - Last QTR] ON Key_Match.ProjID = [vwProject - Last QTR].ProjectID)
LEFT JOIN vwMilestoneTypeNewOld ON Milestone.MilestoneTypeID = vwMilestoneTypeNewOld.MilestoneTypeID;

15

What is the calculation to determine the variance? Let's start with just that...nothing else - how do you want to calculate the number of days.

16

This will not work - the first WHEN statement will stop the CASE expression if [LatestBaselineDate] is NULL. The second WHEN can never be evaluated so it will either return the OriginalBaselineDate or the datediff.

Also, to calculate the column [Milestone - Baseline Date]:

      , [Milestone - Baseline Date] = coalesce(LatestBaselineDate, OriginalBaselineDate)

COALESCE returns the first non-null value...

17

thanks again . the Calculation for Milestone-Variance( in No of Days) = Actual Forecast -OriginalBaselineDate

hope that helps

18

If that is all - then why do you need to case expression at all?

      , [Variance] = datediff(day, OriginalBaselineDate, [Actual-ForecastDate])

Unless - you are trying to calculate one or the other column in the above.

19

No I dont want to Calculate Milestone- baselineDate

MilestoneBaselineDate is an alias of Original Baseline Date . its already a datetime value

the Column we are evaluating is

  1. LatestbaselineDate
    in the first CASE Statement is
    If LatestbaselineDate IS NULL Then we want the column to be left as OriginalBaseline and alias as Milestone Baseline
  1. the Second Case statement is where the issue is and it says 2 things

IF latest baseline IS NULL THEN leave the column as OriginalBaselineDate
IF latestBaseline IS NOT NULL THEN calculate Actual Forecast-OriginalForecase and alias as Milestone Variance

20

Sorry - that doesn't make sense. You cannot have a single case expression return 2 separate columns - is that what you are trying to do?