CODEWITHSUNDEEP
sqlsql-serverbetween
BarcodePiglet
BarcodePiglet

Reputation: 119

Trying to get Month and Day of last year, and year before data

Here I have constructed a query which retrieves the dates for policy expired from today's date and 11 months ago.

As this query does retrieve data, I am needing to change up the query to retrieve data from 1 year ago from the current date, and a year before that.

For example, I would need the policies that were expired between the dates of 5/19/2013 and 5/19/2014.

I am trying to use a between function call, but am not sure how to implement it.

Here is what I have so far for my current data which is displayed below:

Customer Number| Name | EMail | Producer | Policy Expired

abcde-12345-fghij-67890 | Bob Builder | [email protected] | 31000 | Dora Explorer | 2014-07-29
abcde-12345-fghij-67890 | Bob Builder | [email protected] | 31000 | Dora Explorer | 2014-08-07
abcde-12345-fghij-67890 | Bob Builder | [email protected] | 31000 | Dora Explorer | 2014-08-10


  SELECT DISTINCT   
                Customer.custno as [Customer Number], 
                CONCAT(Customer.FirstName, ' ', Customer.LastName) AS Name, 
                Customer.EMail,
                (isnull(Employee.Firstname + ' ','') + isnull(Employee.LastName,'')) AS Producer,
                BasicPolInfo.polexpdate as [Policy Expired]

  FROM 
                Customer INNER JOIN
                BasicPolInfo ON Customer.CustId = BasicPolInfo.CustId INNER JOIN
                Transaction ON BasicPolInfo.PolId = Transaction.PolId INNER JOIN
                GeneralLedgerBranch ON Customer.GLBrnchCode = GeneralLedgerBranch.GLBrnchCode INNER JOIN
                GeneralLedgerDepartment ON Customer.GLDeptCode = GeneralLedgerDepartment.GLDeptCode INNER JOIN
                GeneralLedgerDivision ON Customer.GLDivCode = GeneralLedgerDivision.GLDivCode INNER JOIN
                Employee ON BasicPolInfo.ExecCode = Employee.EmpCode

  WHERE 
                Customer.firstname IS NOT NULL 
                AND Customer.EMail IS NOT NULL 
                AND Customer.Active = 'Y' 
                AND Customer.typecust = 'P'
                AND BasicPolInfo.PolExpDate >= DATEADD(MONTH, -10,CONVERT(VARCHAR(11),GETDATE(),106))               
                AND BasicPolInfo.PolExpDate <= CONVERT(VARCHAR(11),GETDATE(),106) 
                AND BasicPolInfo.status <> 'D'
                AND GeneralLedgerDepartment.Name = 'Personal -'

  ORDER BY      BasicPolInfo.PolExpDate ASC

Above, I am trying to translate the

AND BasicPolInfo.PolExpDate >= DATEADD(MONTH, -10,CONVERT(VARCHAR(11),GETDATE(),106))
AND BasicPolInfo.PolExpDate <= CONVERT(VARCHAR(11),GETDATE(),106)

Into a between 05/19/2013 through 05/19/2014. How would I go about that?

I looked online but could not come to a conclusion.

Upvotes: 3

Views: 70

Answers (1)

JohnS
JohnS

Reputation: 2052

If BasicPolInfo.PolExpDate is a DATE datatype then it as simple as 

AND BasicPolInfo.PolExpDate BETWEEN 
  DATEADD(YEAR, -2, GETDATE()) 
  AND DATEADD(YEAR, -1, GETDATE())

If your datatype is a DATETIME and you need to strip the time part then use

AND BasicPolInfo.PolExpDate BETWEEN
  DATEADD(YEAR, -2, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) 
  AND DATEADD(YEAR, -1, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())))

Upvotes: 2

Related Questions