topic Re: changing date format in a query in Discussions

changing date format in a query

mallen810 — Fri, 09 Dec 2016 20:09:51 GMT

I am putting together a query and would like to make a computed column from my available columns.

When this data is retreived from the sql data base it brings in the date in this format (month, day, year) in separate columns

What I would like to do is have a column returned in my query that has already combined the month, day, year format in a seperate column as (12/1/16) so I can graph the data.

Re: changing date format in a query

Eric_Hill — Sun, 11 Dec 2016 13:25:21 GMT

Hey, mallen810,

Can you provide a little more detail on what you are trying to do, like what the date computation looks like? When the data gets back to JMP, is it a number formatted as a date, or is it character data? Screen shots can be a big help.

There are two possibilities - one is to figure out a way to make the computed date be recognized by JMP as a date, but if that is not possible, the workaround is to add some JSL in the Post Query Script of the query that will convert whatever comes back from the database into a proper JMP date.

Eric

Re: changing date format in a query

pmroz — Mon, 12 Dec 2016 14:42:41 GMT

What database are you querying? I.e. Oracle, SQL Server etc.?

If the original field in the database is already a date, why not just pull that field in directly? JMP will treat that as a date.

If the database has day, month, year in separate fields, you can combine them in SQL. Here's an example using Oracle:

SELECT to_date(to_char(a.month_field) || '/' || 
               to_char(a.day_field)   || '/' || 
               to_char(a.year_field), 'MM/DD/YYYY')
  FROM my_table a

Re: changing date format in a query

mallen810 — Mon, 12 Dec 2016 15:33:35 GMT

The original data is coming in from an SQL server.

The date is recieved in three seperate colums.

I would like to add a date column in this querry from the available columns.

Re: changing date format in a query

mallen810 — Mon, 12 Dec 2016 15:37:06 GMT

Here is the current sql

Re: changing date format in a query

pmroz — Mon, 12 Dec 2016 15:38:40 GMT

In SQL Server you can use the CONVERT or TRY_CONVERT functions to create a datetime value from individual components. This is a good link for you.

Re: changing date format in a query

pmroz — Mon, 12 Dec 2016 15:47:27 GMT

If you have SQL Server 2012 or later you can use the DATEFROMPARTS function:

SELECT DATEFROMPARTS(@t1.year, @t1.month, @t1.day) AS my_date

Re: changing date format in a query

Eric_Hill — Mon, 12 Dec 2016 18:42:56 GMT

Hey,

pmroz's answer is great if you want to generate the date column on the server. If you're okay with the date column being generated in JMP after the query finishes, you could add this JSL code to the Post-Query Script panel in Query Builder:

New Column( "Date",
	Numeric,
	"Continuous",
	Format( "m/d/y", 12 ),
	Input Format( "m/d/y" ),
	Formula( Date MDY( :month, :day, :year ) ),
);

One advantage of doing it this way is that it work regardless of what database you have. Of course, if all you have is SQL Server, you may not care about other databases....

HTH,

Eric

Re: changing date format in a query

mallen810 — Mon, 12 Dec 2016 18:48:50 GMT

Thanks guys for all your responses.

Eric, just added the post query script and it worked perfect.

Thanks again.

mallen810