Trying to get the month and day of the last year, and the year before the data
Here I built a query that returns the policy expiration dates from today and 11 months ago.
Since this query is retrieving data, I need to change the query to retrieve data from 1 year ago from today's date and one year before.
For example, I need policies that expire between 5/19/2013 and 5/19/2014.
I'm trying to use a function call between functions, but I'm not sure how to implement it.
This is what I have for my current data, which is displayed below:
Customer Number| Name | EMail | Producer | Policy Expired
abcde-12345-fghij-67890 | Bob Builder | somewhere@email.com | 31000 | Dora Explorer | 2014-07-29
abcde-12345-fghij-67890 | Bob Builder | somewhere@email.com | 31000 | Dora Explorer | 2014-08-07
abcde-12345-fghij-67890 | Bob Builder | somewhere@email.com | 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
And BasicPolInfo.PolExpDate> = DATEADD (MONTH, -10, CONVERT (VARCHAR (11), GETDATE (), 106))
AND BasicPolInfo.PolExpDate <= CONVERT (VARCHAR (11), GETDATE (), 106)
Between 05/19/2013 and 05/19/2014. How can i do this?
I looked online but couldn't come to a conclusion.
source to share
If BasicPolInfo.PolExpDate is a DATE datatype, then it is as simple as
AND BasicPolInfo.PolExpDate BETWEEN
DATEADD(YEAR, -2, GETDATE())
AND DATEADD(YEAR, -1, GETDATE())
If your datatype is DATETIME and you need to remove the temp part 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())))
source to share