cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

How do I convert Date in seconds to use it in date difference method

HV0508
Level II

I have a code section where I have to create  new column hours by finding the difference between the current date and the minimum date (initial date) . But unfortunately i have tried a lot of things but i could not get to the solution.

 

// Convert "Date" from Character to Date format
col = Column(dtCombined, "Date");
col << Data Type("Numeric", Informat("y/m/d"), Format("d/m/y"));
col << Modeling Type("Continuous");

//Finding the initial date 
initialDate = Col Minimum(dtCombined:Date);
Show("Earliest Date Found:", initialDate);

// Add "Hours" column as the first column
dtCombined << New Column("Hours",
	Numeric,
	Continuous,
	Formula(Date Difference(:Date, initialDate, "Hour"))
);


Following are the things that i have tried - 
None of these return 0 when initial date is subtracted with date value same as initial date - 

Show(Date Difference(24 - 09 - 2024, 3809980800, "Hour"));
Show(Date Difference(24 - 09 - 2024, As Date(3809980800), "Hour"));
Show(Date Difference(As Date(24 - 09 - 2024), 3809980800, "Hour"));
Show(Date Difference(24 - 09 - 2024, As Date(Format(3809980800, "d/m/y", 10)), "Hour"));
Show(Date Difference(24 - 09 - 2024, Informat("3809980800", "d/m/y"), "Hour"));
initialD = Format(3809980800 + Informat("01/01/1904", "m/d/y"), "d/m/y");
Show(initialD);
k = Format(3809980800, "d/m/y");
Show(k);
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: How do I convert Date in seconds to use it in date difference method

This JSL returns a value of 0

Show( Date Difference( datedmy(24,09,2024), 3809980800, "Hour" ) );
Jim

View solution in original post

HV0508
Level II

Re: How do I convert Date in seconds to use it in date difference method

Thankyou Jim , I am just wondering so do i take the day , hour and month out from the Date column's value  and then use datedmy . let me try this 

View solution in original post

7 REPLIES 7
jthi
Super User


Re: How do I convert Date in seconds to use it in date difference method

After you have your date in JMP's datenum format it should be easy as it is and usually Format Pattern is the way to go for that conversion.

jthi_0-1740494594124.png

Can you provide example of your table with few of those date string values? 

-Jarmo
HV0508
Level II


Re: How do I convert Date in seconds to use it in date difference method

HV0508_0-1740499787713.png

HV0508_1-1740499813366.png

 

This is the original data , which i convert to numeric form to get the minimum date(initial date in code ) . 
Then I am using the below code to get the difference between the two . The thing is Date column's date is in d/m/y(24 - 09 - 2024) form where as the initalDate is in seconds

Formula(Date Difference(:Date, initialDate, "Hour"))
jthi
Super User


Re: How do I convert Date in seconds to use it in date difference method

Seconds, d/m/y, q/yyyy,... format doesn't matter as long as you have a date. JMP will handle them as seconds since midnight January 1, 1904 anyway (JMP Help tells you this).

 

What are you trying to do? Find difference between dates? If you are trying to do that, then why are you comparing with ours instead of days? I did mention JMP Help and Scripting Index, both of those will explain what you can do with date difference and what the valid intervalNames are https://www.jmp.com/support/help/en/18.1/#page/jmp/date-and-time-functions.shtml?os=win&source=appli... (Scripting Index has better documentation in this case...)

-Jarmo
txnelson
Super User

Re: How do I convert Date in seconds to use it in date difference method

This JSL returns a value of 0

Show( Date Difference( datedmy(24,09,2024), 3809980800, "Hour" ) );
Jim
HV0508
Level II

Re: How do I convert Date in seconds to use it in date difference method

Thankyou Jim , I am just wondering so do i take the day , hour and month out from the Date column's value  and then use datedmy . let me try this 

jthi
Super User


Re: How do I convert Date in seconds to use it in date difference method

24 - 09 - 2024 as a date is 12/31/1903 23:26:00. JMP Help does have quite ok documentation about date functions https://www.jmp.com/support/help/en/18.1/#page/jmp/date-and-time-functions.shtml#  as does Scripting Index

jthi_0-1740497554589.png

Below are few of the more simple simple options

Date Difference(24Sep2024, 3809980800, "Hour");
Date Difference(Date DMY(24, 9, 2024), 3809980800, "Hour");

Also don't forget to check what alignment does in Date Difference() (it defaults to "start").

 

-Jarmo
HV0508
Level II


Re: How do I convert Date in seconds to use it in date difference method

Cool . Thankyou , let me try and get back