changing date format in a query

[#1]

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.

[#2]

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

[#3]

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

[#4]

Here is the current sql

[#5]

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.

[#6]

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.

[#7]

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

[#8 · Accepted 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

[#9]

Thanks guys for all your responses. 

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

Thanks again.

mallen810