Subscribe Bookmark RSS Feed

changing date format in a query

mallen810

Contributor

Joined:

Dec 9, 2016

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

Joined:

Oct 1, 2013

Solution

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

8 REPLIES
Eric_Hill

Staff

Joined:

Oct 1, 2013

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

Joined:

Jun 23, 2011

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

Contributor

Joined:

Dec 9, 2016

Here is the current sql

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

mallen810

Contributor

Joined:

Dec 9, 2016

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

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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

Joined:

Oct 1, 2013

Solution

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

Contributor

Joined:

Dec 9, 2016

Thanks guys for all your responses. 

 

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

 

Thanks again.

 

mallen810