Agfdhyk

I receiving over API a json with datetimes formatting like:
December 4, 2018 11:10:00 AM (so it is like: mm-dd--year hh-mm-ss am/pm)
I added a datetime row to the table in my MS SQL Server 2016 and import the data. I expected that the datetime displayed like 2018-12-04 11:10:00.000 but on Import process the SQL Server kills the time part entries. So only 2018-12-04 00:00:00.000 is stored. I checked Google and stackoverflow but found the reason for that.

Any tips?

Edit

I stored the json Infos in a variable and Import the data via @json/OPENJSON and merge it via tsql "merge" into the table.

The datetime format in use is a cummulation of all the never-do-this in one package... It is language specific, culture specific and non-standard.

But - lucky guy - you are very close to an existing standard format in SQL-Server. Read about CAST() and CONVERT() and the third parameter..

Try this:

SET LANGUAGE ENGLISH;

DECLARE @YourDateTimeString VARCHAR(100)='December 4 2018, 11:10:00 AM';



SELECT CONVERT(DATETIME,REPLACE(@YourDateTimeString,',',''),109);

Make sure to use a language which knows "December". In my culture (German) this was "Dezember" and the script would fail...

It seems to be enough to replace the comma in order to get into format 109...

If there is any chance to change the input, try to convince the sending side to use a standard format, best was ISO8601 (yyyyMMddTHH:mm:ss).

UPDATE

While testing this I had the comma in the wrong spot. With the string given by you this works even without any replacements:

SET LANGUAGE ENGLISH;

DECLARE @YourDateTimeString VARCHAR(100)='December 4, 2018 11:10:00 AM';



SELECT CONVERT(DATETIME,@YourDateTimeString,109);