none
Convert MS Access vba function to sql function

    Question

  • I created a simple function in MS Access to determine what shift was running at my job based on a timestamp. I am now converting our Access databases to sql server. I need a little help converting this to a sql "Scalar-valued Function" (I think this is the type of function I need). Any help would be appreciated. I am sure I could figure it out in a couple of weeks on my own but sooner is better.

    Thanks

    Public Function Time2Shift(tm As Date)
    Dim td As Double
    Select Case Weekday(tm)
        Case 1
            GoTo SS
        Case 2
            GoTo MTTF
        Case 3
            GoTo MTTF
        Case 4
            GoTo Wed
        Case 5
            GoTo MTTF
        Case 6
            GoTo MTTF
        Case 7
            GoTo SS
    End Select
    MTTF: 'Monday, Tuesday, Thursday, Friday

            Select Case CDbl((Format(tm, "hhmm")) & "." & (Format(tm, "ss")))
                Case 0 To 659.59
                Time2Shift = 1
                Case 700 To 1459.59
                Time2Shift = 2
                Case 1500 To 2259.59
                Time2Shift = 3
                Case 2300 To 2359.59
                Time2Shift = 1
            End Select
    Exit Function
    Wed: 'Wednesday
            Select Case CDbl((Format(tm, "hhmm")) & "." & (Format(tm, "ss")))
                Case 0 To 659.59
                Time2Shift = 1
                Case 700 To 1859.59
                Time2Shift = 2
                Case 1900 To 2359.59
                Time2Shift = 1
            End Select
    Exit Function
    SS:         'Saturday Sunday
            Select Case CDbl((Format(tm, "hhmm")) & "." & (Format(tm, "ss")))
                Case 0 To 1059.59
                Time2Shift = 1
                Case 1100 To 2259.59
                Time2Shift = 3
                Case 2300 To 2359.59
                Time2Shift = 1
            End Select
    End Function

    Friday, July 26, 2013 9:17 PM

Answers

  • Try this,

    CREATE FUNCTION Time2Shift(@TM AS DATETIME)
    RETURNS DECIMAL(10,2)
    BEGIN
    --	DECLARE @TM AS DATETIME
    --	SET @TM = GETDATE()
    	DECLARE @Time2Shift INT,@TEMP DECIMAL(10,2)
    	SET @TEMP = CAST(REPLACE(CONVERT(CHAR(5),@TM,108),':','') AS NVARCHAR)  +  '.' + CAST(DATEPART(S,@TM) AS NVARCHAR)
    	SET @TEMP = CAST(@TEMP AS DECIMAL(10,2))
    	IF DATENAME(DW,GETDATE()) IN ('MONDAY','TUESDAY','THURSDAY','FRIDAY')
    	BEGIN
    		IF @TEMP BETWEEN 0 AND 659.59 SET @Time2Shift = 1
    		ELSE IF @TEMP BETWEEN 700 AND 1459.59 SET @Time2Shift = 2
    		ELSE IF @TEMP BETWEEN 1500 AND 2259.59 SET @Time2Shift = 3
    		ELSE IF @TEMP BETWEEN 2300 AND 2359.59 SET @Time2Shift = 1	
    	END
    	ELSE IF DATENAME(DW,GETDATE()) IN ('WEDNESDAY')
    		BEGIN	
    			IF @TEMP BETWEEN 0 AND 659.59 SET @Time2Shift = 1
    			ELSE IF @TEMP BETWEEN 700 AND 1859.59 SET @Time2Shift = 2
    			ELSE IF @TEMP BETWEEN 1900 AND 2359.59 SET @Time2Shift = 1	
    		END
    	ELSE IF DATENAME(DW,GETDATE())  IN ('SATURDAY','SUNDAY')
    		BEGIN
    			IF @TEMP BETWEEN 0 AND 1059.59 SET @Time2Shift = 1
    			ELSE IF @TEMP BETWEEN 1100 AND 2259.59 SET @Time2Shift = 3
    			ELSE IF @TEMP BETWEEN 2300 AND 2359.59 SET @Time2Shift = 1	
      
    		END
    	RETURN @Time2Shift
    END
    ------------------------------------
    SELECT DBO.Time2Shift(GETDATE())


    Regards, RSingh

    Friday, July 26, 2013 10:27 PM

All replies

  • Try this,

    CREATE FUNCTION Time2Shift(@TM AS DATETIME)
    RETURNS DECIMAL(10,2)
    BEGIN
    --	DECLARE @TM AS DATETIME
    --	SET @TM = GETDATE()
    	DECLARE @Time2Shift INT,@TEMP DECIMAL(10,2)
    	SET @TEMP = CAST(REPLACE(CONVERT(CHAR(5),@TM,108),':','') AS NVARCHAR)  +  '.' + CAST(DATEPART(S,@TM) AS NVARCHAR)
    	SET @TEMP = CAST(@TEMP AS DECIMAL(10,2))
    	IF DATENAME(DW,GETDATE()) IN ('MONDAY','TUESDAY','THURSDAY','FRIDAY')
    	BEGIN
    		IF @TEMP BETWEEN 0 AND 659.59 SET @Time2Shift = 1
    		ELSE IF @TEMP BETWEEN 700 AND 1459.59 SET @Time2Shift = 2
    		ELSE IF @TEMP BETWEEN 1500 AND 2259.59 SET @Time2Shift = 3
    		ELSE IF @TEMP BETWEEN 2300 AND 2359.59 SET @Time2Shift = 1	
    	END
    	ELSE IF DATENAME(DW,GETDATE()) IN ('WEDNESDAY')
    		BEGIN	
    			IF @TEMP BETWEEN 0 AND 659.59 SET @Time2Shift = 1
    			ELSE IF @TEMP BETWEEN 700 AND 1859.59 SET @Time2Shift = 2
    			ELSE IF @TEMP BETWEEN 1900 AND 2359.59 SET @Time2Shift = 1	
    		END
    	ELSE IF DATENAME(DW,GETDATE())  IN ('SATURDAY','SUNDAY')
    		BEGIN
    			IF @TEMP BETWEEN 0 AND 1059.59 SET @Time2Shift = 1
    			ELSE IF @TEMP BETWEEN 1100 AND 2259.59 SET @Time2Shift = 3
    			ELSE IF @TEMP BETWEEN 2300 AND 2359.59 SET @Time2Shift = 1	
      
    		END
    	RETURN @Time2Shift
    END
    ------------------------------------
    SELECT DBO.Time2Shift(GETDATE())


    Regards, RSingh

    Friday, July 26, 2013 10:27 PM
  • thanks i'll try it in a few minutes
    Saturday, July 27, 2013 12:51 AM
  • Thank You. It works perfectly. 
    Saturday, July 27, 2013 1:42 AM