585738_en-US

liliconcom

Re: jmp not recognize sql datetime2 datatype as date

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

jmp not recognize sql datetime2 datatype as date

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? 

Data AccessWindowsRe: jmp not recognize sql datetime2 datatype as date

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

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

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

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.