cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
CC86
Level II

extract date from date time variable

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_dateCST_timeResult Cortisol 1Date Cortisol 1Result Cortisol 2Date Cortisol 2Result Cortisol 3Date Cortisol 3Result Cortisol 4
30/04/20185:16:00 AM1804/30/2018 5:34 AM3004/30/2018 7:58 AM   
22/06/20185:26:00 AM306/22/2018 5:14 AM1806/22/2018 6:14 AM   
14/07/20168:11:00 AM1307/14/2016 7:45 AM4607/14/2016 8:47 AM5007/14/2016 9:17 AM 
26/04/20188:48:00 AM804/25/2018 6:13 AM1204/26/2018 8:03 AM   
23/02/20178:38:00 PM4002/23/2017 3:49 PM3502/23/2017 6:55 PM3602/23/2017 9:07 PM42
25/12/20191:33:00 PM1012/25/2019 12:23 PM     
28/05/20209:06:00 AM405/28/2020 6:58 AM2005/28/2020 10:26 AM   
29/09/20177:45:00 PM1909/29/2017 7:04 PM3009/29/2017 8:53 PM   
01/12/20172:47:00 PM1012/01/2017 2:45 PM2012/01/2017 3:54 PM   
22/06/20171:49:00 PM206/22/2017 1:10 PM2406/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. 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: extract date from date time variable

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

Jim

View solution in original post

6 REPLIES 6
txnelson
Super User

Re: extract date from date time variable

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
	)
);
Jim
CC86
Level II

Re: extract date from date time variable

Thank you so much! This worked perfectly. 

CC86
Level II

Re: extract date from date time variable

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!

txnelson
Super User

Re: extract date from date time variable

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

Jim
CC86
Level II

Re: extract date from date time variable

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

Jeff_Perkinson
Community Manager Community Manager

Re: extract date from date time variable

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

 

Using dates, times, datetimes and durations in JMP 

-Jeff