Greetings experts,
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
I believe I have found the solution to my issue.
The code below updates the records with end of month date values.
update MonthlyStatus set EOM_Date=(DATEADD(dd, 0, DATEDIFF(dd, 0, EOMONTH(getdate())))),
LastUpdated=(DATEADD(dd, 0, DATEDIFF(dd, 0, EOMONTH(getdate())))), Alerted=1, Alerted=1type or paste code here
This query will display end of current month of the current year.
select DATEADD(dd, 0, DATEDIFF(dd, 0, EOMONTH(getdate())))
From everything I read so far about EOMONTH function, it still produces correct date value even on a leap year.
Why did you include the additional dateadd/datediff functions? If that is just to convert the data to a datetime data type - it isn't necessary. If the data type of EOM_Date is a date data type, then you are converting from a date data type, to a datetime - them implicitly converting it back to date.
If you really need to convert the date from EOMONTH to a datetime then: CAST(EOMONTH(getdate()) AS datetime)
As to why you need to update this table every month - that isn't clear. Do you really have a table with 2 columns that defines the EOM date and last updated date? With an Alerted column set to 1?
If so - then there is no reason to update the Alerted column since it will always be set to 1 and the LastUpdated column isn't needed. If not, then your update is not correct and it will update every row in the table to have the same EOM date.
Hi @jeffw8713,
Your points are very well taken.
This app ( we jokingly call it spaghetti, was built in 2007 and has never been updated since then - until now.
I started working with this organization last year and I have been tasked with rewriting it from scratch after I have determined the app is not redeemable especially the DB that is highly denormalized with absolutely no relationships between tables.
The app is supposed to automatically run their master report at end of each month if not run before then.
Worse still, the developer of this app's idea of running a report is to update this MonthlyStatus table with info I provided earlier.
The app does not run the app and as a result, the page breaks during app load on browser.
In nutshell, this attempt to update that table the end of every month is intended to keep the app running while buying me time to rewrite the app.
The DB has to be redesigned first - SIGH!
To your point about date info, yes, I appreciate your suggestion and will make the change accordingly.
Thanks for your help