1

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.

Rahul Neekhra
  • 780
  • 1
  • 9
  • 39
TimoC
  • 687
  • 2
  • 6
  • 15
  • 7
    `... and import the data` <= You need to include **how** you are importing the data. – Igor Nov 08 '18 at 11:24
  • 3
    What's the type of the column you've inserted the data into? What code are you using for the import? What code are you using when extracting/displaying the data? – JohnLBevan Nov 08 '18 at 11:27
  • Hi Guys, sorry to miss that Import point. 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 column type is datetime as written. – TimoC Nov 08 '18 at 12:00
  • In the future please [edit] your question to add the additional details instead of putting them in a comment. Then it becomes more visible to others who might not read the comments. – Igor Nov 08 '18 at 13:12
  • Have you looked at this previous answer yet? https://stackoverflow.com/a/1135756/1260204 – Igor Nov 08 '18 at 13:15
  • Please try to set up a *stand-alone* running example. Just some code one can execute to see what you want to achieve. Show the wrong output and explain what you want to get instead. This is called [MCVE](https://stackoverflow.com/help/mcve). – Shnugo Nov 08 '18 at 14:21

1 Answers1

1

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);
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114