pb sqlquery

Question

Hi,

i use in a sqlquery (post jsl) this line:

column(dt,"Date de fab")<<data type(Numeric)<<Modeling Type(Ordinal)<<Format("d/m/y", "-1", "NO", "");

but when I execute the query, the date column in the JMP file is not good.

you can see that the date is: month/day/year

the property of the column is good: day/month/year, and it's the same for the xls file.

I don't understand why I haven't the same format ijn the jmp file.

thanks for your help.

best regards

gzmorgan0 · Answer

Here is the code that works for an Excel file (indat.xlsx) with one Date column in m/d/y format. Note you might have to change the DSN name for your machine server name.  I created the query with the UI below is the source script attached to the JMP table it created and the Date column was d/m/y. Check the log to see if the post script had errors.

New SQL Query(
	Connection(
		"ODBC:DSN=Excel Files;DBQ=C:	emp\indate.xlsx"
	),
	QueryName( "Sheet1$" ),
	Select( Column( "Date", "t1", Numeric Format( "m/d/y h:m:s", "0", "NO", "" ) ) ),
	From( Table( "Sheet1$", Alias( "t1" ) ) ),
	PostQueryScript(
		"column(\!"Date\!") << Data type(Numeric) <<Modeling Type(Ordinal) << Format(\!"d/m/y\!", -1, \!"No\!",\!"\!");"
	)
) << Run

Françoise · Answer

hi,

I have the beginning of the sql' s jsl like this:

t= Current Data Table();

//Paramétrage de colonnes

column("Date de fab")<<data type(Numeric)<<Modeling Type(Ordinal)<<Format("d/m/y", "-1", "NO", "");

No change with your answer.

gzmorgan0 · Answer

Try this:

change column(dt, "Date de fab") to Column("Date de fab")

I don't think the data table has a reference.