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

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

HV0508 — Tue, 25 Feb 2025 15:28:25 GMT

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);

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

jthi — Tue, 25 Feb 2025 14:44:08 GMT

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.

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

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

txnelson — Tue, 25 Feb 2025 15:08:01 GMT

This JSL returns a value of 0

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

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

jthi — Tue, 25 Feb 2025 15:33:05 GMT

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

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").

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

HV0508 — Tue, 25 Feb 2025 16:15:11 GMT

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"))

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

HV0508 — Tue, 25 Feb 2025 16:16:18 GMT

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

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

HV0508 — Tue, 25 Feb 2025 16:16:48 GMT

Cool . Thankyou , let me try and get back

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

jthi — Tue, 25 Feb 2025 16:35:26 GMT

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=application#ww2538966 (Scripting Index has better documentation in this case...)