时间戳

首页 > 解决方案 > 将 DATEDIFF 从 SQL Server 转换为 redshift

sql - 将 DATEDIFF 从 SQL Server 转换为 redshift

问题描述

我有需要转换为 redshift 的 SQL Server 脚本。

这是其中的一部分，我有问题

LEFT JOIN       
    (SELECT  
         cog.ClientId, 
         MAX(CASE me.metrickey WHEN 'contacts-employee_active_count_day_org' THEN mu.value ELSE 0 END) AS ActiveEmployees
     FROM    
         public.module_utilization mu
     JOIN    
         (SELECT  
              me.id,
              me.ChannelId + '-' + me.metrickey AS MetricKey
          FROM    
              public.module_metric me) AS me ON me.id = mu.metricid
     LEFT JOIN       
         public.contacts_client_organization cog ON cog.clientorganizationid = mu.organizationid
     WHERE   
         mu.dy >= DATEADD(Day, -30, GETDATE())
         AND me.metrickey IN ('contacts-employee_active_count_day_org')
     GROUP BY
         cog.clientid) metrics ON metrics.ClientId  = be.clientid
WHERE           
    be.organizationid = 65277
    AND be.timeworkedfrom >= DATEADD(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
    AND be.timeworkedfrom < TO_CHAR(DATE_TRUNC('month', GETDATE()), 'MM/DD/YYYY')
    AND be.isdeleted IS NULL
    AND be.isvoid IS NULL

在这一行

AND be.timeworkedfrom >= DATEADD(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

我收到这个错误

无效操作：函数 pg_catalog.date_diff("unknown", integer, timestamp without time zone) 不存在；

据我了解，因为0

我该如何解决这个问题？

标签： sqlsql-serveramazon-redshift

解决方案

您突出显示的WHERE子句逻辑与timeworkedfrom比当月第一天早六个月的日期进行比较。你可以改变这个：

AND be.timeworkedfrom >= DATEADD(MONTH, -6, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

对此：

AND be.timeworkedfrom >= date_trunc('month', current_date) - interval '6 month'

推荐阅读