|
Create PROCEDURE NextGen_ConvertDate_Deven AS
declare @DLSStart smalldatetime
set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))
/* please Note this funcution (fn_GetDaylightSavingsTimeStart) returns values is 2008-03-09 02:00:00 */
update NexGen_New_Losses_Deven
set F37_CLAIM_ENTERED_S_DT_conv =
CASE
WHEN XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS'
AND F37_CLAIM_ENTERED_S_DT <= @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -5 ,F37_CLAIM_ENTERED_S_DT ) --WINTER
WHEN XM_REPORTING_MCO_NM = 'HUDSON EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'CHARLOTTE EXPRESS OFFICE'
OR XM_REPORTING_MCO_NM = 'INDIANAPOLIS EXPRESS'
AND F37_CLAIM_ENTERED_S_DT > @DLSStart -- 2008-03-09 02:00:00
THEN dateadd (hour, -4 ,F37_CLAIM_ENTERED_S_DT )
ELSE NULL
END
------------------------------------
when I run this stored procedure, it updates only first case of WHEN for each state, no matter what date I entered here. for example, always it reduce 5 hours for hudson, charlotte and Indianapolis and not reducing 4 hours for any date of year. I have a doubt that stored procedure doesn't like my @DLSStart variable. please advice...
Thanks
Deven
| 
