Specific format/formula when changing column datatype [duplicate]

Question

Can one modify a column's datatype using a very specific conversion format?

For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system default which is 109 I believe.

Is there any way to achieve this without creating a temporary column?

Answer 1

First and foremost:

• Why are you storing dates as text?

Second, as I pointed out in comments, there is an accepted answer of Aaron Bertrand that states: 'Sorry, not possible', you cannot change the default conversion behaviour. And that is why I flagged this question as duplicated.

And third, having a look at Microsoft SQL Server 2008 Internals, at chapter: Internals of Altering Tables. (bold is mine)

Another negative side effect of altering tables happens when a column is altered to increase its length. In this case, the old column is not actually replaced. Rather, a new column is added to the table, and DBCC PAGE shows you that the old data is still there. I’ll let you explore the page dumps for this situation on your own, but we can see some of this unexpected behaviour by just looking at the column offsets using the column detail query that I showed you earlier in this chapter.

Another drawback to the behaviour of SQL Server in not actually dropping the old column is that we are now more severely limited in the size of the row. The row size now includes the old column, which is no longer usable or visible (unless you use DBCC PAGE).

You can check the length by using DATALENGTH function:

DECLARE @dt datetime = GETDATE();
DECLARE @sdt varchar(20) = CONVERT(varchar, GETDATE(), 20);

SELECT @dt AS dt, DATALENGTH(@dt) AS dt_lenght,
       @sdt AS sdt, DATALENGTH(@sdt) AS sdt_lenght
GO

dt                      | dt_length | sdt                 | sdt_length
:---------------------- | --------: | :------------------ | ---------:
2020-05-30 10:50:51.587 |         8 | 2020-05-30 10:50:51 |         19

db<>fiddle here