Choose Language Hide Translation Bar
ileshem
Community Trekker

Changing a date and time column to yield only the date.

Hello again,

 

I would like help with changing a date and time column to yield only the date. 

I have changed the date and time column to show only the date via the column info but when I'm tring to make a vairability chart using the date as an X axis, I am still getiing the date and the time.

 

Thanks in advance.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Changing a date and time column to yield only the date.

Datetime values are stored as the number of seconds since January 4, 1904 00:00:00 (midnight).

 

Date values are also stored as the number of seconds since midnight, January 4, 1904 to midnight on the date in question.

 

Since both values are stored as seconds, you just need to subtract the time of day portion from a datetime value to get the date value.

//today() returns the current date and time
datetime=today();

//subtract the time portion of datetime
date=datetime - time of day(datetime);

//show datetime and date as seconds and then format date to see the date
show(datetime, date, format(date, "mm/dd/yy"));

 

This can also be done in the formula editor to create a new column with the date. The easiest way is to right click at the top of the datetime column and choose New Formula->Date Time->Date.

JMPScreenSnapz187.png

 

-Jeff

View solution in original post

3 REPLIES 3
cwillden
Super User

Re: Changing a date and time column to yield only the date.

hi @ileshem,

Data formats don't change the actual underlying values, just how they are displayed.  There may be a better way to do this, but I often just create a new column where I take the value in the original data-time column and format it with a column formula like so:

Format(:Column 1, "m/d/y")

This drops the time data so that it is effectively changed to the 12:00:00 am.  Then, I delete the column formula and change the new column's format to the desired date format in Column Info.

-- Cameron Willden
0 Kudos
vince_faller
Super User

Re: Changing a date and time column to yield only the date.

could always turn the time in seconds to days and floor it then turn it back into seconds

 

x = today();
show(MDYHMS(x));
show(MDYHMS(floor(x/(60*60*24))*60*60*24));
Vince Faller - Predictum
0 Kudos
Jeff_Perkinson
Community Manager Community Manager

Re: Changing a date and time column to yield only the date.

Datetime values are stored as the number of seconds since January 4, 1904 00:00:00 (midnight).

 

Date values are also stored as the number of seconds since midnight, January 4, 1904 to midnight on the date in question.

 

Since both values are stored as seconds, you just need to subtract the time of day portion from a datetime value to get the date value.

//today() returns the current date and time
datetime=today();

//subtract the time portion of datetime
date=datetime - time of day(datetime);

//show datetime and date as seconds and then format date to see the date
show(datetime, date, format(date, "mm/dd/yy"));

 

This can also be done in the formula editor to create a new column with the date. The easiest way is to right click at the top of the datetime column and choose New Formula->Date Time->Date.

JMPScreenSnapz187.png

 

-Jeff

View solution in original post