Sage 100

I am using 2014 Visual Integrator. I want to add a where statement to my ODBC SQL statement for Today's date. I have tried WHERE InvoiceDate = now() without success. Any suggestions. I always find date selecting the most difficult.

Try InvoiceDate = DAY

This is in the SQL query not inside the VI front end. Do I need DAY, DAY(), {DAY} or some other iteration?

Or create a calculated field with DAY as the calculation then refer to that in your WHERE clause.

Calculated fields can be passed to an ODBC SQL statement?

Are you using a SQL query to create the source file?

@LarryBradford try select cast (GETDATE() as DATE) to retrieve the today without the time component.

There is no Now() in SQL and dates are stored in binary with a time stamp so it's WAY too precise unless you restrict it to examining just the Date portion.

I wrote a VI export job that used the following on the record selection for the DateUpdated field to pull only the Sales Orders where the date updated is equal to the current date: EVS(""DTE(0:""""YYYYMMDD"""")"")

I found and tried the cast(getdate() as date) and the now() both through ETG (Education Through Google) without success. My problem is the Sales Order number increment when I use the Selection Tab.

@LarryBradford I'm still unclear about source file and import or export goal. If

Sorry. Message got away from me. If you'd like to give me the big picture by PM, I'll try to help.

What I am trying to do is create Purchase Orders from a Sales Order (I know you can use Sage functionality). I do not want to use the Select tab in the import because then numbers get skipped in the sequential assignment and the import runs slower. I was modifying the SQL statement (using an ODBC source). The command to do this in the WHERE statement of the SQL tab is ={fn CURDATE()}. I found it hear. The source does not reference their source. http://sagecity.na.sage.com/support_communities/sage100_erp/f/98/t/77567.aspx

Moira (or anyone else that might have an idea) - I am trying to export sales orders through VI with a ship date of ""tomorrow"". I found your EVS(""DTE(0:""""YYYYMMDD"""")"") Select formula in this thread which works great for ""today's"" stuff, but not sure how/if I can edit to add a day. Is this possible? I tried putting a ""1"" and a ""+1"" where you have the 0, but it didn't work. Any thoughts?

The EVS is evaluating a string, this is where I got the info http://manual.pvxplus.com/PXPLUS/functions/evs.htm#format1. Prompt for the date at run time is not option for you?

Yeah, I'm reading that same article now, but there are no related examples for different days. This will be an automated export, so no, can't prompt. I'm going to check with my development team to see if I can still use this function or not.

Amber ... even if you do get '+1' to work, do you really want tomorrow, or is it more like the next business day?

Luckily, in this case, this will be a 7 day/week export since they deliver every day.

We got the EVS function to work for ""tomorrow"" with this Select formula: EVS(""DTE(JUL(DTE(0))+1:""""YYYYMMDD"""")"")

Thanks for posting the resolution @AmberPrayfrock