jmp not recognize sql datetime2 datatype as date

[#1]

When I query datetime2 data type columns from sql tables they come back as character variables rather than numeric dates in the JMP data type. how do I cast convert in the sql so that it will come back as a numeric column type when I query with JMP? datetime? 

[#2]

What is the structure of the data when it is returned as a character string?  (i.e. 01/15/2022, or 01AN2022, or ....)

[#3]

I have the same problem. We use the German Version of JMP. When I import a table from Microsoft SQL-Server I get a date if the datatype is datetime (well) and when I import the datatime2(7) I get only a string.

Example for datetime (Correct)

In SQL-Server: TraceLevelBird5_0-1715941465648.png Import in JMP: TraceLevelBird5_1-1715941590303.png Query result:  TraceLevelBird5_2-1715941699567.png

Example for datetime2 (not recognized as date, only as string)

In SQL-Server: TraceLevelBird5_3-1715941925447.png  Import in JMP: TraceLevelBird5_4-1715942162248.png Query result:TraceLevelBird5_5-1715942364441.png

How can I get datetime2 is date in JMP?

Thanks

[#4]

Hi,

It seems as if the problem still exists in JMP18.1 when quering a "datatime2(0)" format in Microsoft SQL server.

Is there a solution to this which I have not found in JMP?

Thanks,

Sven

[#5]

Not sure why it sees it as character data, but we just cast DATETIME2 data as DATETIME within our queries.

SELECT CAST(ReadTime AS DATETIME) AS ReadTime FROM....

[#6]

JMP sees it as Character data since that's the way the driver presents it.

Normally JMP parses the string into a JMPDateTime (numeric). datetime2 has the fractional seconds which doesn't conform to ISO 8601so it isn't parsed into numeric data. That being said, it does look like it'n now part of the ISO. I've entered a note to evaluate parsing datetime2 data when importing data into JMP.

Thanks.