MS SQL Server 2012 exports datetime as a script that it cannot import

Question

I am using SQL Server 2012 Express. The database has a [config] table with a member of type 'datetime'. Exporting it (only data) using Management Studio as an SQL script generates:

INSERT [dbo]. [config] ([id], [name], [creation_date], ...) VALUES (13, N'Test ', CAST (N'2014-11-17 09:29: 07.047' AS DateTime), .. .)

Now, when executing the generated script, he complains that a datetime value is not valid. I can manually fix this by replacing the space between date and time with “T”:

INSERT [dbo]. [config] ([id], [name], [creation_date], ...) VALUES (13, N'Test ', CAST (N'2014-11-17 T 09: 29: 07.047' AS DateTime) ,. ..)

Looking into the docmentation datetime, a space format seems not to be supported.

How can I generate scripts with the supported datetime format (ie including "T")?

How to import a format that uses space without changing the imported script?

By the way, it looks like it works on other installations of SQL Server, but I cannot find the difference. Also uninstalling and reinstalling SQL Server did not help.

Answer 1

How to import a format that uses space without changing the imported script?

You need to change the import script, but a bit. Specify dateformat as ymd at the beginning of the script.

 set dateformat ymd; 

How can I generate scripts with the supported datetime format (i.e. incl. 'T')?

Vote for change ...

Generate scripts for data, datetime scripts in locale-specific format

Answer 2

A dateformat statement is required. Once SET DATEFORMAT is in place, I don't even need to use CAST AS .

Also note that your date format depends on where you are in the world. While in Denmark, mine is in DD-MM-YYYY format and has a 24-hour clock, so my dateformat instruction will be DMY , not YMD . If the sequence is incorrect, you are likely to receive an error message that exceeds the allowed value. I did.:)

Here is an example of code that now worked for me using Microsoft's own SQL editor:

 SET DATEFORMAT DMY INSERT INTO mytable ([id],[page],[saved]) VALUES ('2','29','16-01-2017 13:53:22') 

Happy coding, assistant.