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?

  • @McNets that question you linked has a "sorry, not possible" as the accepted answer, how is that a duplicate? – jitbit May 30 at 9:12
  • Why not change the column to datetime2(0) instead? – Marcello Miorelli May 30 at 13:39
  • Why do you want to do this? You will lose the original datetime column and make the data much harder to work with. I would rather format it in the application or create a computed column with the converted value or something. It's not clear what you want to accomplish by doing this. Unlike for example postgres an alter column doesn't have a using so I'm afraid if you really want to do this your best option is to create a new column, write the data using an update, and remove the old column. – Tom V Jun 3 at 15:41
  • @TomV-trytopanswers.xyz the datetime bit was "for example" I'm actually converting sql_variant to make it deterministic. – jitbit Jun 3 at 20:30

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
dt                      | dt_length | sdt                 | sdt_length
:---------------------- | --------: | :------------------ | ---------:
2020-05-30 10:50:51.587 |         8 | 2020-05-30 10:50:51 |         19

db<>fiddle here

| improve this answer | |
  • 1
    "Why are you storing dates as text" well, it's more complicated than that. It's actually sql_variant, b/c the database uses "Entity-Attribute-Value" pattern for UDF (user-defined-fields) and I need to make that UDF data searchable via FT-index. But I can't create an indexed view from sql_variant (b/c its non deterministic) so I'm actually converting UDF to strings from sql_variant – jitbit May 30 at 10:18
  • @jitbit check this very similar question and convert to datetime2 instead - by the same McNets mind you ;) – Marcello Miorelli May 30 at 13:43
  • @jitbit then I think this should be the question. According Aaron you can't alter the default cast behaviour. – McNets May 30 at 14:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.