SQLTeam.com | Weblogs | Forums

Convert datetime column to text value in another column

Hi everyone,

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?

Welcome

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?

I would not recommend you do that.

Hey,

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.

Thanks!

1 Like

select concat('(',month(getdate()),') ', DATENAME(mm,getdate()) )

for testing purposes

;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)

Thank you, appreciate your help!

or maybe another approach would be instead of bastardizing the whole table with this abomination :smile: 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.

1 Like