MS SQL Server 2012 exports datetime as script which it cannot import

Question

I'm using SQL Server 2012 Express. The database has a table [config] with a member of type 'datetime'. Exporting it (data only) by Management Studio as 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 it complains that the value for the datetime type is invalid. I can manually fix it by replacing the blank between the date and the time by a "T":

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

Looking into the docmentation of datetime, the format using a blank seems to be not supported.

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

How can I import the format that uses a blank without changing the imported script?

BTW, it looks like it works on other SQL Server installations, but I cannot find the difference. Also uninstalling and reinstalling SQL Server didn't help.

Answer 1

How can I import the format that uses a blank without changing the imported script?

You have to change the import script but just a little. Specify dateformat as ymd at the beginning of the script.

set dateformat ymd;

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

Vote for a change...

"Generate scripts" for data, scripts datetime in a locale dependent format

Answer 2

A dateformat instruction is all is takes. Once the SET DATEFORMAT is in place, I don't even need to use the CAST AS.

Also note that your date format depends on where you are in the world. Being in Denmark, mine are in the format DD-MM-YYYY and 24 hour clock, so my dateformat instruction would be DMY rather than YMD. If the sequence is wrong, you will probably get an error about value exceeding the permitted value. I did. :)

Here is an example of code that worked for me just now 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, mate.