如何使用区间来计算sql的日期范围?

I have a two tables in one table I have a column contravention date and in other table i have NTO date column now I want to find how can I retrieve data from tables where NTOdate is greater than 50 days of contravention date. but when I run this query i get error as Incorrect syntax near '50'.

我有两个表在一个表中，我有一个列违反日期，在另一个表中我有NTOdate列，现在我想要找到如何从NTOdate大于50天的违反日期的表中检索数据。但是当我运行这个查询时，我在“50”附近出错，语法错误。

SELECT [ICPS].[dbo].[tickets].t_vrm
  ,[ICPS].[dbo].[tickets].t_zone_name
  ,[ICPS].[dbo].[tickets].t_street_name
FROM [ICPS].[dbo].[tickets]
INNER JOIN [ICPS].[dbo].[ticket_events]
  ON [ICPS].[dbo].[ticket_events].[te_system_ref] = [ICPS].[dbo].[tickets].[t_number]
WHERE [ICPS].[dbo].[tickets].[t_camera_ticket] = '0'
  AND [ICPS].[dbo].[tickets].[t_date_time_issued] >= convert(DATETIME, '2012/10/01', 101)
  AND [ICPS].[dbo].[ticket_events].[te_event_code] = '300'
  AND [ICPS].[dbo].[ticket_events].[te_date] > DATE (DATE_SUB([ICPS].[dbo].[tickets].[t_date_time_issued], INTERVAL 50 days))

1 个解决方案

#1

You can use Datediff function: DateDiff(DD,'firstDate','SecondDate'). Here is the sample:

您可以使用Datediff函数:Datediff (DD，“firstDate”，“SecondDate”)。这是示例:

select [ICPS].[dbo].[tickets].t_vrm,[ICPS].[dbo].[tickets] .t_zone_name, [ICPS].[dbo].[tickets] .t_street_name from  [ICPS].[dbo].[tickets]  inner join 
      [ICPS].[dbo].[ticket_events] on [ICPS].[dbo].[ticket_events].[te_system_ref] = [ICPS].[dbo].[tickets].[t_number] 
where[ICPS].[dbo].[tickets].[t_camera_ticket] = '0' and  [ICPS].[dbo].[tickets].[t_date_time_issued] >= convert(datetime,'2012/10/01',101)
      and [ICPS].[dbo].[ticket_events].[te_event_code] = '300' and 
     DateDiff(DD, [ICPS].[dbo].[tickets].[t_date_time_issued], [ICPS].[dbo].[ticket_events].[te_date]) >50)