Sript As Insert function adds character T for datetime column

ssurankar

Every time I use this function (Script as INSERT) on a result set that contains datetime values, then clicking on Script as INSERT option, I noticed that a character 'T' is added in the date column value. It automatically gets placed between date and time. E.g. If I run a select statement to get values from a column of data type - datetime I get the result as  '2019-06-01 00:00:00.000' and after using Script as Insert option on this , then code given in new window is as below

: CREATE TABLE #temptable ( [DATE OF SERVICE] datetime ) INSERT INTO #temptable VALUES ( N'2019-06-01T00:00:00' ) DROP TABLE #temptable


Notice that there is a T added in the values clause. Due to this I get hard time to troubleshoot further issues occurred due to this. Is there any specific reason why character T is added or Is there any setting due to which it adds a T if yes please let me know how I can prevent it from inserting or is this a bug ? Awaiting for your response.

Thanks
Sushant Surankar

Tianjiao_Li

Hi Sushant,

The T is added because of the ISO 8601 format: https://en.wikipedia.org/wiki/ISO_8601
The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.

Can you please post your idea on user voice forum https://redgate.uservoice.com/forums/94413-sql-prompt