SQL: Consolidate Calculation with Function/Procedure?

Question

I have a block of code to adjust the date for my tables.

    dateadd(hh,
case
    when
        month(planned_start)=1 or
        month(planned_start)=2 or
        month(planned_start)=11 or
        month(planned_start)=12 then -5
    when
        month(planned_start)=3 then
            case
                when datepart(hh,planned_start)<6 then -5
            else -4
            end
    else -4
end
,planned_start)

Basically, I have to adjust my dates for a time difference, but I need to adjust the date in multiple spots in my SQL. Right now, I just have this block of code repeated about 8 times.

Is there a way to make this into a function or procedure so I can just send 'planned_start' to the function, run the calculation, and return the '-4 or -5' needed to adjust the dates?

Note: I don't believe I can just use a variable, since 'planned_start' and the hour offset could be different per row of data.

Thanks.

Answer 1

Here is a simplified version of your query

SELECT Dateadd(hh, CASE
                     WHEN Month(planned_start) IN ( 1, 2, 11, 12 )
                           OR ( Datepart(hh, planned_start) < 6
                                AND Month(planned_start) = 3 ) THEN -5
                     WHEN Month(planned_start) = 3
                          AND Datepart(hh, planned_start) >= 6 THEN -4
                     ELSE -4
                   END, planned_start) 

and yes you can create a function and use it your query instead of repeating the big chunk of code

CREATE FUNCTION dbo.Dateadjust (@DATE DATETIME)
RETURNS DATETIME
AS
  BEGIN
      DECLARE @result DATETIME; -- previously it was int 

      SELECT @result = Dateadd(hh, CASE
                                     WHEN Month(@DATE) IN ( 1, 2, 11, 12 )
                                           OR ( Datepart(hh, @DATE) < 6
                                                AND Month(@DATE) = 3 ) THEN -5
                                     WHEN Month(@DATE) = 3
                                          AND Datepart(hh, @DATE) >= 6 THEN -4
                                     ELSE -4
                                   END, @DATE)

      RETURN( @result );
  END;

GO 

To use it in SELECT statement

select planned_start,dbo.Dateadjust(planned_start)
From yourtable