topic Re: extract date from date time variable in Discussions

extract date from date time variable

CC86 — Fri, 09 Jun 2023 23:38:41 GMT

Hello, I have ~20 columns of result dates which are formatted as numeric, continuous m/d/y h: m with width of 19. I want to see if these results occur on the same date ("CST_date") as another variable that is formatted as numeric, continuous, d/m/y width 12. I am trying to make a new column where if the result 1 date is equal to CST_date , put in value from result 1. Is it possible to do this without making a new column with the date from the results column extracted (no time)?

CST_date	CST_time	Result Cortisol 1	Date Cortisol 1	Result Cortisol 2	Date Cortisol 2	Result Cortisol 3	Date Cortisol 3	Result Cortisol 4
30/04/2018	5:16:00 AM	18	04/30/2018 5:34 AM	30	04/30/2018 7:58 AM
22/06/2018	5:26:00 AM	3	06/22/2018 5:14 AM	18	06/22/2018 6:14 AM
14/07/2016	8:11:00 AM	13	07/14/2016 7:45 AM	46	07/14/2016 8:47 AM	50	07/14/2016 9:17 AM
26/04/2018	8:48:00 AM	8	04/25/2018 6:13 AM	12	04/26/2018 8:03 AM
23/02/2017	8:38:00 PM	40	02/23/2017 3:49 PM	35	02/23/2017 6:55 PM	36	02/23/2017 9:07 PM	42
25/12/2019	1:33:00 PM	10	12/25/2019 12:23 PM
28/05/2020	9:06:00 AM	4	05/28/2020 6:58 AM	20	05/28/2020 10:26 AM
29/09/2017	7:45:00 PM	19	09/29/2017 7:04 PM	30	09/29/2017 8:53 PM
01/12/2017	2:47:00 PM	10	12/01/2017 2:45 PM	20	12/01/2017 3:54 PM
22/06/2017	1:49:00 PM	2	06/22/2017 1:10 PM	24	06/22/2017 3:10 PM

I tried the following but it is not working.

data table ("cstlabs")<< new column ("basal_cst_cort", formula (if (

:cst_date == formula (:Date Cortisol 1 - time of day (:Date Cortisol 1)), :Result Cortisol 1, 0)));

Even when I write

data table ("cstlabs") << new column ("date1", time of day (:Date Cortisol 1)); it is not working.

Any help would be greatly appreciated.

Re: extract date from date time variable

txnelson — Thu, 24 Sep 2020 02:13:06 GMT

Here is a script that should work

Names Default To Here( 1 );
Data Table( "cstlabs" ) << New Column( "basal_cst_cort",
	formula(
		If( :cst_date == :Date Cortisol 1 - Time Of Day( :Date Cortisol 1 )
		),
		:Result Cortisol 1,
		0
	)
);

Re: extract date from date time variable

CC86 — Thu, 24 Sep 2020 11:01:32 GMT

Thank you so much! This worked perfectly.

Re: extract date from date time variable

CC86 — Thu, 24 Sep 2020 13:48:01 GMT

Thank you very much. The above script works but I do not really understand what is going on with this dataset/ time variables. I have multiple time variables stored as h:m. When I subtract them from each other like:

data table ("cstlabs")<< new column ("test", formula (

time of day(:timecst) -time of day(:timecortisol1))); the variable comes out as an integer, but if I write like this:

data table ("cstlabs")<< new column ("test", formula (

:timecst -:timecortisol1)) --> this comes out as a very strange number.

I think I am having a very difficult time using these variables, because I do not understand why this happens. For example, with the following code my entire column is blank:

data table ("cstlabs")<< new column ("AM_cort", formula (

if(hour (:Date Cortisol 1)>= 5 & hour (:Date Cortisol 1) <=9 & time of day (:time) <= time of day (:timecortisol1) , :Result Cortisol 1, 0) ));

Thank you so much!

Re: extract date from date time variable

txnelson — Thu, 24 Sep 2020 19:22:41 GMT

A JMP datetime value is just a numeric, continuous value, which is the number of seconds since midnight, January 1, 1904. A time value is just the number of seconds since midnight today. If you subtract one time value from another, you will get the a duration value, which is the number of seconds between the two times. As for determining the exact issue you may be having, you would have to attach the data table to this discussion

Re: extract date from date time variable

CC86 — Tue, 29 Sep 2020 02:08:27 GMT

Jim - thank you so much. I always struggle with time/date and mostly all of JMP and really find your answers helpful. Thank you!

Re: extract date from date time variable

Jeff_Perkinson — Tue, 29 Sep 2020 13:13:14 GMT

If you struggle with dates and times in JMP here's a primer that might help you: