Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2012
    Posts
    17

    Unanswered: UDF case statement

    Hi all,

    I am trying to create a UDF which adjusts the time according to daylight saving and the timezone the user chooses. There is no error when running this code, but the problem is the result does not change. The output time does not change even if I change the region in the parameters, it just outputs the original time!! Hope someone can help me!!

    Code:
    CREATE FUNCTION [dbo].[get_TimZone_HourDiff_new]
    (@InputDate datetime, @Region varchar(3))
    RETURNS datetime
    WITH EXEC AS CALLER
    AS
    BEGIN
    DECLARE @returnDate Datetime
     
        RETURN(CASE 
                               WHEN @Region = 'PST' THEN
                                      CASE 
                                          WHEN @InputDate >= '02-Nov-2013' 
                                                 THEN @InputDate + (8/24)
                                          ELSE @InputDate + (7/24) 
                                          END
                               WHEN @Region = 'EST' THEN
                                      CASE 
                                          WHEN @InputDate >= '02-Nov-2013' 
                                                 THEN @InputDate + (5/24)
                                          ELSE @InputDate + (4/24) 
                                          END
                               WHEN @Region = 'MST' THEN
                                      CASE 
                                          WHEN @InputDate >= '02-Nov-2013' 
                                                 THEN @InputDate + (7/24)
                                          ELSE @InputDate + (6/24) 
                                          END
                            ELSE
                            @InputDate
                          END)
    END
    Thanks,
    Alan
    Last edited by akira919; 11-15-13 at 03:45.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    All of the fractions use integer math, and evaluate to 0. You should use the DATEADD function, instead.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wouldn't using GETUTCDATE (Transact-SQL) be a lot easier and more flexible?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    to add to Pat's Post ..

    The local or lawful time is a display issue. You need to do it in the front end off of UTC in the schema. When MS gets up to the ANSI/ISO standards you will have some options in the the DDL; Google it and get ready when they catch up

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •