Date formatting in Number Edit Box

[#1]

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:

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.

Is there some warkaround for this?

Or a different approach entirely?

Thanks in advance.

[#2 · Accepted 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.

[#3]

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