Subscribe Bookmark RSS Feed

Re: Date formatting in Number Edit Box

itsalkskeith

New Contributor

Joined:

Sep 4, 2017

Hi,

 

I'm trying to get my date formatted in a particular way. 

I've already looked at http://www.jmp.com/support/help/Date-Time_Functions_and_Formats.shtml for the formatting but see no option that is appropriate for the format I need. 

Currently I have it set as follows:

startDate = As Date( Today() - 86400 ); //get current time for yesterday
startDateEB = Number Edit Box( startDate, 10 );
startDateEB << Set Format( Format( "yyyy-mm-dd" ) );

Which gives boxes like this:

Capture.PNG

 

The contents of this number edit box is inserted into a query later on(so needs to be in a format like 2017-11-28 07:30:00), and the above works fine when wanting to query data for entire days, but when you want to pull a specific window of time it doesn't allow for this.

 

I've attempted changing the Set Format(Format()) paramaters

startDateEB << Set Format( Format( "yyyy-mm-dd hh:mm" ) );

But Format won't accept this and just returns the numeric value of the date-time.Capture2.PNG

 

Is there some warkaround for this?

Or a different approach entirely?

 

 

Thanks in advance.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

Try putting a T between the date and time portion. 

 

startDateEB << Set Format( Format( "yyyy-mm-ddThh:mm:ss" ) );

 

From the Numeric Formats section of the help page for the Column Info dialog box (text edit boxes use the same formats):

 

Time Formats
When you choose a Time format, you can also specify an Input Format. The Time format indicates how the time appears in the data table cells, and the Input Format indicates how you enter the time.
You can add the number of hours, minutes, and seconds after midnight of the prepended date for the following date formats:
m/d/y
d/m/y
y/m/d
ddMonyyyy
Monddyyyy
Locale Date
For example, December 31, 2004 has a numeric value of 3,187,296,600, which represents 12/31/2004 12:10 AM.
:day:hr:m and :day:hr:m:s show the number of days, hours, minutes, and seconds since January 1, 1904. For example, the results for December 31, 2004 are :36890:00:10: and :36890:00:10:00.
h:m:s and h:m show the hours, minutes, and seconds portion of the date in the date field. For example, the results for December 31, 2004 at 12:10 AM are 12:10:00 AM and 12:10 AM.
yyyy-mm-ddThh:mm and yyyy-mm-ddThh:mm:ss show the year, month, day, and time. For example, 2004-12-31T12:10:00. T is a literal value, representing itself.
2 REPLIES
Highlighted
ih

Community Trekker

Joined:

Sep 30, 2016

Solution

Try putting a T between the date and time portion. 

 

startDateEB << Set Format( Format( "yyyy-mm-ddThh:mm:ss" ) );

 

From the Numeric Formats section of the help page for the Column Info dialog box (text edit boxes use the same formats):

 

Time Formats
When you choose a Time format, you can also specify an Input Format. The Time format indicates how the time appears in the data table cells, and the Input Format indicates how you enter the time.
You can add the number of hours, minutes, and seconds after midnight of the prepended date for the following date formats:
m/d/y
d/m/y
y/m/d
ddMonyyyy
Monddyyyy
Locale Date
For example, December 31, 2004 has a numeric value of 3,187,296,600, which represents 12/31/2004 12:10 AM.
:day:hr:m and :day:hr:m:s show the number of days, hours, minutes, and seconds since January 1, 1904. For example, the results for December 31, 2004 are :36890:00:10: and :36890:00:10:00.
h:m:s and h:m show the hours, minutes, and seconds portion of the date in the date field. For example, the results for December 31, 2004 at 12:10 AM are 12:10:00 AM and 12:10 AM.
yyyy-mm-ddThh:mm and yyyy-mm-ddThh:mm:ss show the year, month, day, and time. For example, 2004-12-31T12:10:00. T is a literal value, representing itself.
itsalkskeith

New Contributor

Joined:

Sep 4, 2017

Thanks this solved my issue completely!
I thought the SQL query would be unable to deal with the T but had no impact.