title: Trigger code or date format error in SQL Server 2008 R2
body: <p>I am using a capturing software, Abbyy FlexiCapture, after user done the verification (some sort of user key in data). It will export captured data into SQL Server 2008 R2 </p>
  
  <p>Once SQL Server identify there is an <strong>inserted data</strong> (new row), it will <strong>trigger</strong> code to <strong>update</strong> table Abbyy base on business rule.</p>
  
  <p>The table has two columns called <code>ProcessingDate</code> and <code>DateOfService</code></p>
  
  <p>My business rule requirement is </p>
  
  <ol>
  <li>If Processing Date is later than Date Of Service 7 month, it will update Table Abbyy 
  <ul>
  <li>column <code>CouponStatus</code> with "<strong>Reject</strong>",</li>
  <li>column <code>RejectCode</code> with "<strong>A5</strong>".</li>
  </ul></li>
  <li>If Processing Date is earlier than Date Of ServiceDate 7 month, it will update Table Abbyy
  <ul>
  <li>column <code>CouponStatus</code> with "Approve",</li>
  <li>column <code>RejectCode</code> with "null".</li>
  </ul></li>
  </ol>
  
  <p>I am facing an error because some inserted data with Processing Date <strong>more than</strong> Service Date 7 month, column <code>CouponStatus</code> become "<strong>Approve</strong>". </p>
  
  <p>I am not sure is my trigger code problem or date format problem.
  when user do validation, the date format is <code>dd/mm/yy</code>, </p>
  
  <p>In SQL Server 2008 R2, my <code>ProcessingDate</code> and <code>DateOfService</code> column's datatype is <code>date</code> with format <code>yyyy-mm-dd</code></p>
  
  <p>Here are my trigger code, pls focus start on line 86</p>
  
  <pre><code>USE [master]
  GO
  /****** Object:  Trigger [dbo].[BusinessRule]    Script Date: 10/03/2012 11:28:05 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  -- =============================================
  -- Author: &lt;Author,,Name&gt;
  -- Create date: &lt;Create Date,,&gt;
  -- Description: &lt;Description,,&gt;
  -- =============================================
  
  ALTER TRIGGER [dbo].[BusinessRule]
  ON [dbo].[Abbyy]
  AFTER INSERT
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  
  DECLARE @Identity varchar(225);
  DECLARE @RegNo varchar(225);
  DECLARE @ProDate date;
  DECLARE @SerDate date;
  DECLARE @PriKey varchar(255);
  
  SELECT @Identity=EngineNo, @RegNo=VehRegNo, @ProDate=ProcessingDate, @SerDate=DateOfService,@PriKey=DocID FROM Inserted
  
  --If EngineNo not exist in db, update Reject &amp; A1
  IF EXISTS (Select EngineNo
              From Abbyy
              Where
                  NOT EXISTS
                  (Select EngineNo
                   From eDaftarOwnerDetail
                   where eDaftarOwnerDetail.EngineNo = @Identity))
  
      UPDATE Abbyy
      SET CouponStatus = 'Reject', RejectCode = 'A1'
      WHERE EngineNo = @Identity
      and DocID=@PriKey
  
  --If Vehicle Registration No not exist in db, update Reject &amp; A2
  Else If EXISTS (Select VehRegNo
                  From Abbyy
                  Where
                      NOT EXISTS
                      (Select VehRegNo
                       From eDaftarOwnerDetail
                       Where eDaftarOwnerDetail.VehRegNo = @RegNo))
      UPDATE Abbyy
      SET CouponStatus = 'Reject', RejectCode = 'A2'
      WHERE VehRegNo = @RegNo
      and DocID=@PriKey
  
  --If EngineNo &amp; Vehicle Registration No does not matched, update Reject &amp; A3
  Else If EXISTS (Select EngineNo, VehRegNo
                  From Abbyy
                  Where
                      NOT EXISTS
                      (Select EngineNo, VehRegNo
                       From eDaftarOwnerDetail
                       Where eDaftarOwnerDetail.EngineNo = @Identity
                       and eDaftarOwnerDetail.VehRegNo = @RegNo))
      UPDATE Abbyy
      SET CouponStatus = 'Reject', RejectCode = 'A3'
      WHERE EngineNo = @Identity
      and VehRegNo = @RegNo
      and DocID=@PriKey
  
  -- If EngineNo exist in db more then twice, update Reject &amp; A4
  Else If EXISTS (Select COUNT(1)
                  From Abbyy
                  Where EngineNo = @Identity
                  Group by EngineNo 
                  Having COUNT(1)&gt;2)
      UPDATE Abbyy
      SET CouponStatus = 'Reject', RejectCode = 'A4'
      WHERE EngineNo = @Identity
      and DocID=@PriKey
  
  -- If ProcessingDate more than ServiceDate 210 days, update Reject &amp; A5 
  Else If EXISTS (Select ProcessingDate, DateOfService
                  From Abbyy
                  Where
                  datediff(day, @SerDate, @ProDate)&gt;210)
      UPDATE Abbyy
      SET CouponStatus = 'Reject', RejectCode = 'A5'
      WHERE ProcessingDate = @ProDate
      and DateOfService = @SerDate
      and DocID=@PriKey
  
  Else
  UPDATE Abbyy
  Set CouponStatus = 'Approve', RejectCode = ''
  WHERE EngineNo = @Identity
  
  -- Insert statements for trigger here
  
  END
  </code></pre>
  
  <p>Appreciate anyone can give me some guideline to solve this problem. Thank you.</p>