cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mallen810
Level III

changing date format in a query

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Eric_Hill
Staff

Re: changing date format in a query

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

View solution in original post

8 REPLIES 8
Eric_Hill
Staff

Re: changing date format in a query

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

 

pmroz
Super User

Re: changing date format in a query

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
mallen810
Level III

Re: changing date format in a query

Here is the current sql

Screen Shot 2016-12-12 at 10.35.42 AM.png

mallen810
Level III

Re: changing date format in a query

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.

Screen Shot 2016-12-12 at 10.24.25 AM.pngScreen Shot 2016-12-12 at 10.23.58 AM.png

 

pmroz
Super User

Re: changing date format in a query

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.

 

 

pmroz
Super User

Re: changing date format in a query

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

 

Eric_Hill
Staff

Re: changing date format in a query

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

mallen810
Level III

Re: changing date format in a query

Thanks guys for all your responses. 

 

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

 

Thanks again.

 

mallen810