Sorry for the (hopefully) easy-to-answer question, I am really new to SQL and haven't successfully been able to figure this one out yet!
The scenario is:
We have a datetime column (stat_date_time_resolved) and I am trying to grab the value in that column and convert it to a varchar value in a separate column (resolved month).
e.g. 1
stat_date_time_resolved = 2022-01-24 18:11:33.863
created month would = (1) January
e.g. 2
stat_date_time_resolved = 2022-04-13 04:33:11.732
created month would = (4) April
I need to update all records in our table with these month values. Can anyone start me off in the right direction please?
the month() function would do that for you. But why are creating such a column, what is the business requirement/use case? What would happen if the original stat_date_time_resolved changes? Will you need to update this new column etc?
Thanks for the reply! The purpose of this is so we can report on a dashboard some year over year stats separated by month. Due to the limitations of the application front end that we're designing the dashboard in, we needed to capture the month (by name) in a separate column for each of the records in order to present the data in this manner.
Would you happen to have an example of the month() function for this particular use case? I understand the month() function will display the month as a numerical value, would it be possible to then display it as a text value as well?
As for if/when that original datetime column changes - we have something in the front end application that calculates the month for us in the separate column automatically. This process is simply to update records that were created prior to us building the calculation in the front end.
Again adding a column just for reporting purposes creates a lot of overhead. Just add it to the view or stored procedure used vy the dashboard. Dont add a column on the table
I understand that, but our front end application where we build the dashboards cannot reference a view or stored procedure, it can only reference data in a column. It's an application restriction that's causing us to build it in this manner.
Are you using some sort of orm? I still think you can accomplish this client side. (Php? Angular/js?)
Anyways...
So you want the final result to be (1) January
Yeah the final result in the 'Created Month' column would display as '(1) January'.
Our front end is a no-code application built in .NET; the dashboard functionality within there is drag and drop and you're limited to the options therein; of which there is (unfortunately) no modifier we can apply onto the date/time fields to display them in a month name format as we needed. The graph that displays this info will be a bar chart separated by month with stacked values year over year per month.
;with numbers
as(
select distinct dateadd(mm,column_id,getdate()) as mydate
from sys.all_columns
where column_id between 1 and 12
)
select concat('(',month(mydate),') ', DATENAME(mm,mydate) )
from numbers
order by month(mydate)
or maybe another approach would be instead of bastardizing the whole table with this abomination create a new table that holds these values and join to it from that other table?
formatteddate month
(1) January 1
(2) February 2
(3) March 3
(4) April 4
(5) May 5
(6) June 6
(7) July 7
(8) August 8
(9) September 9
(10) October 10
(11) November 11
(12) December 12
then no need to do
"we have something in the front end application that calculates the month for us in the separate column automatically. "
I wouldn't update a column for this - I would use a computed column instead. That would avoid the unnecessary updates and if the source column changes it would automatically be updated.
create a new table that holds these values and join to it from that other table?