Query a multiple dates from a previous year

[Flafel]

Good day,

I am a novice to SQL, but learning...

I have an issue with a table, where there are a series of incorrect dates for 2017 that were added to the table that are the same month/day for 2016. I need to isolate those identical month/days for 2017. I have looked into both the DateSerial() command and the Day() command, without any luck retrieving multiple records and only able to retrieve a single day.

Example of issue for the table:
RecordId#, OpenDate
1, 9/15/2016
2, 9/15/2017 (this should be 9/14/2017)
3, 9/13/2018

Again, having the issue of retrieving the "all records" with the same month and day of the previous year, not just a single date as shown in the example. Dumb question, am I using the correct the syntax, WHERE Year(2016) and Day([OpenDate]) = Year(2017) and Day([OpenDate])....or syntax, WHERE DateSerial (2017,[OpenDate],9) = DateSerial(2016,[OpenDate],9)? However, neither statements are working? My apologies, if I am not providing enough information to help. Thank you in advance for your help.

[CJ_London]

Suspect not enough information provided but you probably need to use two queries

1. create a query called allDates

SELECT dateadd("y",1,openDate) as NextYear
FROM myTable
WHERE Year(openDate)=2016

2. create another query based on allDates

SELECT myTable.*
FROM myTable INNER JOIN allDates ON myTable.openDate= allDates.NextYear
WHERE year(openDate)=2017

This should produce a list of all records in 2017 that have the same month/day as 2016

[Gicu]

Or create a calculated field in the query to get you the previous year\ same day and month:

PrevYear: DateSerial(Year([OpenDate])-1,Month([OpenDate]),Day([OpenDate]))

Now use this field in your join.

Cheers,

[Flafel]

Thank you to you both Vlad and Ajax!
Appreciate the assistance and those suggestions got me where I needed to be.

Take care