I have SQL Server backend and Access frontend. Three of my tables are Contacts, Settings, and Tournaments. Contacts is pretty obvious and includes a DOB field. Settings is used for setting default values for specific functions including identifying the current tournament. Tournaments list all the tournaments and details, including the tournament date.
In SQL server I have a scalar function for calculating date difference in years. I use it in the Contacts table for a calculated field for Age based on the current date - ([dbo].[GetDateDiffY]([DOB],getdate())). Works as I want it to.
I also need to calculate the contact's age, in years, on the date of the tournament. I need to be able to have SQL server identify the current tournament set in Settings and get the current tournament's date from the Tournaments table. I can create a calculated field using something similar to - ([dbo].[GetDateDiffY]([DOB],tournamentDate())). I need your assistance to define tournamentDate() in SQL server.
I know I can do this in Access. I have functions in Access called GetDefTournament() and GetDefTournamentDate(). I want to do this in SQL and let SQL do the work.
Any suggestions/assistance are appreciated.