sql - 如何在 t-sql 中的 where 条件下转换并转换为日期时间？

Question

我正在尝试从 vis_p_activities_final 表中获取上个月的数据并尝试以下查询：

SELECT * FROM DPICD.vis_p_activities_final 
WHERE  DPICD.vis_p_activities_final.current_activity_code IN ( 
              'SDBPR', 'SDBPU', 'XSDBPU', 'SDBPUX' ) 
       AND CAST((DPICD.vis_p_activities_final.raised_date_time) AS DATETIME) >= 
           Dateadd(month, -1, CURRENT_TIMESTAMP)`

raise_date_time 列是 VARCHAR，我想将其转换为过滤器的日期时间。我怎样才能做到这一点？

标签： sqltsqldatetimecastingtype-conversion

Answer 1

你可以使用临时表作为

declare @lastmonth datetime
   set @lastmonth = Dateadd(month, -1, CURRENT_TIMESTAMP)`

   SELECT *, CAST((DPICD.vis_p_activities_final.raised_date_time) AS DATETIME) as 
   'revised_date' into #tmp FROM DPICD.vis_p_activities_final 
   WHERE  DPICD.vis_p_activities_final.current_activity_code IN ( 
              'SDBPR', 'SDBPU', 'XSDBPU', 'SDBPUX' ) 
       `

   select * from #tmp where revised_date >= @lastmonth