locked
Need s2ss functions ssma_datediff please RRS feed

  • Question

  • Hi All,

    Even after installing SSMA Extn Pack, we are not able to get the compatibility functions like: ssma_datediff, etc.

    can anyone provide the source code of ssma_datediff ?

    Thanks in Advance !

    Thanks

    VKK

    Wednesday, June 8, 2016 9:01 AM

Answers

  • Hello,

    There are no "source code", many of the SSMA function are implemented as a CLR. Please follow the installation instruction for SSMA step by step (e.g. enabling CLR, Import the CLR assembly, etc).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 8, 2016 12:16 PM

All replies

  • Hello,

    There are no "source code", many of the SSMA function are implemented as a CLR. Please follow the installation instruction for SSMA step by step (e.g. enabling CLR, Import the CLR assembly, etc).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, June 8, 2016 12:16 PM
  • Hi VasanthKK,

    Here it is.

    Mathieu VINCENT - UMANIS

    CREATE FUNCTION [s2ss].[ssma_datediff] (
       @datepart varchar(20), 
       @date1 datetime, 
       @date2 datetime)
    RETURNS int
    as
    BEGIN
      
      IF UPPER(@datepart) = 'HOUR' OR UPPER(@datepart) = 'HH'
        RETURN CONVERT(int, 86400000.0*(convert(numeric(38,30), @date2)
                   - convert(numeric(38,10), @date1))/1000.0/3600.0)
    
      IF UPPER(@datepart) = 'MINUTE' OR UPPER(@datepart) = 'MI'
        RETURN CONVERT(int, 86400000.0*(convert(numeric(38,30), @date2)
                   - convert(numeric(38,10), @date1))/1000.0/60.0)
    
      IF UPPER(@datepart) = 'SECOND' OR UPPER(@datepart) = 'SS'
        RETURN CONVERT(int, 86400000.0*(convert(numeric(38,30), @date2)
                   - convert(numeric(38,10), @date1))/1000.0)
    
      IF UPPER(@datepart) = 'YEAR' or UPPER(@datepart) = 'YY'
        RETURN DATEDIFF(year, @date1, @date2)
    
      IF UPPER(@datepart) = 'QUARTER' or UPPER(@datepart) = 'QQ'
        RETURN DATEDIFF(quarter, @date1, @date2)
    
      IF UPPER(@datepart) = 'MONTH' or UPPER(@datepart) = 'MM'
        RETURN DATEDIFF(month, @date1, @date2)
    
      IF UPPER(@datepart) = 'WEEK' or UPPER(@datepart) = 'WK'
        RETURN DATEDIFF(week, @date1, @date2)
    
      IF UPPER(@datepart) = 'DAY' or UPPER(@datepart) = 'DD'
        RETURN DATEDIFF(day, @date1, @date2)
    
      IF UPPER(@datepart) = 'DAYOFYEAR' or UPPER(@datepart) = 'DY'
        RETURN DATEDIFF(dayofyear, @date1, @date2)
    
      IF UPPER(@datepart) = 'WEEKDAY' or UPPER(@datepart) = 'DW'
          RETURN CONVERT(int, datediff(day, @date1, @date2)/7)
    
      IF UPPER(@datepart) = 'MILLISECOND' or UPPER(@datepart) = 'MS'
        RETURN DATEDIFF(MILLISECOND, @date1, @date2)
    
      IF UPPER(@datepart) = 'CWK' or UPPER(@datepart) = 'CALWEEKOFYEAR'
          RETURN DATEDIFF(week, @date1, @date2)
    
      IF UPPER(@datepart) = 'CDW' or UPPER(@datepart) = 'CALDAYOFWEEK'
          RETURN CONVERT(int, datediff(day, @date1, @date2)/7)
    
      IF UPPER(@datepart) = 'CYR' or UPPER(@datepart) = 'CALYEAROFWEEK'
          RETURN s2ss.ssma_datepart('CYR',@date2) - s2ss.ssma_datepart('CYR',@date1)
    
      RETURN NULL
    
    END
    GO
    • Proposed as answer by mat974 Friday, November 23, 2018 1:38 PM
    Friday, November 23, 2018 1:37 PM