topic jmp not recognize sql datetime2 datatype as date in Discussions

jmp not recognize sql datetime2 datatype as date

bjbreitling — Sat, 10 Jun 2023 20:54:18 GMT

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?

Re: jmp not recognize sql datetime2 datatype as date

txnelson — Sat, 31 Dec 2022 14:09:56 GMT

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

Re: jmp not recognize sql datetime2 datatype as date

TraceLevelBird5 — Fri, 17 May 2024 10:43:21 GMT

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: Import in JMP: Query result:

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

In SQL-Server: Import in JMP: Query result:

How can I get datetime2 is date in JMP?

Thanks

Re: jmp not recognize sql datetime2 datatype as date

Quarnbred — Tue, 22 Oct 2024 11:11:47 GMT

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

Re: jmp not recognize sql datetime2 datatype as date

mmarchandFSLR — Tue, 22 Oct 2024 12:11:31 GMT

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....

Re: jmp not recognize sql datetime2 datatype as date

bryan_boone — Thu, 24 Oct 2024 21:29:18 GMT

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.