When I run the following query:
SELECT EOMONTH(DATEFROMPARTS(YEAR(getDate()), [number], 1)) EndOfEachMonth FROM master..spt_values WHERE [number] BETWEEN 1 AND 12 AND [type] = 'P'
It produces end of each month for the current year.
We have a table called MonthlyStatus with the following field names:
EOM_Date - datetime datatype
Alerted bit datatype
AlreadyRun bit datatype
LastUpdated dateTime datatype.
We would like to update this table every month with both EOM_Date and LastUpdated assigned the value of the end of the current month of the current year.
Is it possible to accomplish this with the help of the query I pasted above?
Is there an easier way to solve this?
I know I can schedule it to run every month but want to make sure that EOM_Date and LastUpdated have value of end of current month while the columns with bit data type get value of (1) or true.
Many thanks in advance