143489_en-US

liliconcom

dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does not work

I have other scripts where I am using a calendar date box to get a date range to select rows from a table where >= the date range and it works.  I have one script where it will not work.

 

I am storing the date in a varaible startDT2.  The format matches what is in the table after I split the table.  Below is the code.

 

TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
CharDate3= Uppercase(Format(TempstartDT, "mm/dd/yyyy"));
startDT2 = CharDate3;
dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy"));

 

When I run the code in debugger I can see that startDT2 is 01/29/2019 when I select that date.  The data has rows where the LOGIN_DATe colulumn has 01/29/2019.  It ends up ignoring the select where and bringing in all of the data.

 

Does anyone know why the Select Where is not working?

 

 

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

Thank you for your help.  I ended up needing to subset the data first then the select where worked.

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

What does Informat() return?

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

This code works.  I entered a date prior to 12/19/2018 and both rows were highlighted.

dt5 = New Table( "Test", Add Rows( 2 ),
 New Column( "PRODUCT_CODE", Character( 6 ), "Nominal",
  Set Values( {"11111", "222"} ), Set Display Width( 103 )
 ),
 New Column( "LOT_NUMBER", Character, "Nominal",
  Set Values( {"11111-01", "222-01"} ), Set Display Width( 88 )
 ),
 New Column( "LOGIN_DATE", Numeric, "Continuous",
  Format( "m/d/y", 12 ), Input Format( "m/d/y" ),
  Set Values( [3628022400, 3631564800] ),
  Set Display Width( 82 )
 ),
 New Column( "Value", Numeric, "Nominal", Format( "Best", 12 ),
  Set Values( [0.0028, 0.001] ), Set Display Width( 91 )
 )
);
nw = new window("test", modal(),
 start = number edit box(),
 start << Set Format( Format( "m/d/y", 12 ) );
);
TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
dt5 << Select where( :LOGIN_DATE >= tempstartdt);

Maybe put some show() statements into your code to see what it's doing?

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

PRODUCT_CODE      LOT_NUMBER   LOGIN_DATE       VALUE_1

11111                       12345-01    12/19/2018     0.0028 

 

See attached file.

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

All dates are in seconds, but you can choose how you want them displayed (i.e. m/d/y).  Can you post a small sample of your data?

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does nTempstartDT is in seconds. The CharDate3 converts it to mm/dd/yyyy. The column LOGIN_DATE is numeric with the format of m/d/y. The above suggestion does not work.Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

What's the format of the LOGIN_DATE column?  If it's numeric (i.e. date) then you don't need to convert to character and then back to numeric date.

TempstartDT = start << get; // This gets the date from the Calendar/Date Box.
dt5 << Select where( :LOGIN_DATE >= tempstartdt);

 

Re: dt5 << Select where (:LOGIN_DATE >= informat(startDT2,"mm/dd/yyyy")); does n

The calendar date box captures a numeric value that represents the number of seconds since midnight, January 1, 1904.

 

The comparisn you want to make should not use a character string as produced by the formatting functions.