Why are you storing dates as INT? To get the number of days difference, you're going to need to convert to DATE.

Try something like this: -

IF OBJECT_ID('abc') IS NOT NULL

BEGIN

DROP TABLE abc;

END

CREATE TABLE abc (gg_key INT, fg_sta_key INT, fg_eff_dt_key INT, fg_end_dt_key INT, fg_seq_nbr INT);

INSERT INTO abc

VALUES (19925, 2, 20130128, 20130128, 1);

INSERT INTO abc

VALUES (19925, 8, 20130128, 20130128, 2);

INSERT INTO abc

VALUES (19925, 1, 20130129, 20130416, 3);

INSERT INTO abc

VALUES (19925, 5, 20130417, NULL, 4);

SELECT gg_key, fg_sta_key, fg_eff_dt_key, fg_end_dt_key, fg_seq_nbr,

DATEDIFF(d,DATE_fg_eff_dt_key,DATE_fg_end_dt_key) AS diffDays

FROM abc

CROSS APPLY (SELECT CAST(CAST(ISNULL(fg_eff_dt_key,20790606) AS VARCHAR(9)) AS DATE) AS DATE_fg_eff_dt_key,

CAST(CAST(ISNULL(fg_end_dt_key,20790606) AS VARCHAR(9)) AS DATE) AS DATE_fg_end_dt_key) ca;

That results in: -

gg_key fg_sta_key fg_eff_dt_key fg_end_dt_key fg_seq_nbr diffDays

----------- ----------- ------------- ------------- ----------- -----------

19925 2 20130128 20130128 1 0

19925 8 20130128 20130128 2 0

19925 1 20130129 20130416 3 77

19925 5 20130417 NULL 4 24156

That isn't using those stupidly stored dates as dates. e.g. If you use "20130301" and "20130401", your code will tell us that there are -660,305 "DiffDays". Whereas from a human perspective, we can see that there is 1 month so we know that since there are 31 days in March that the answer is 31. Obviously I'm assuming that the OP is storing dates as INT, but with column names that are called "dt_key" and data that looks so much like a date, I think that's a safe assumption.