Innovator SQL Procedures vs. Date Format - how to prevent conversion to American date format?
Hi community, I use an Innovator SQL procedure to get some data that is merged with a second database. The SQL procedure is called from a server Method When I test the procedure in SQL, date properties like modified_on are shown as in correct regular Innovator db format: But the SQL procedure doesn´t return the date in this neutral UTC format to the Server Method. Instead of the shown date in the SQL, I get a converted date that looks like this: 7/13/2002 1:49:27 PM In my case the date is later displayed in a Form, but in my country nobody uses this date format. I am able convert the date into correct DMY-format in C# or SQL. But these steps are unnecessary complicated cause of the American date in between. I wonder why I get the American MDY-date at the first place. I use German locals for Innovator and the server itself. Does anyone know, what can cause dates to be converted to American format when using SQL procedures? Is there away to prevent it? Thanks for any hint! Angela
How to search for 'Yesterday'?
I want to find all part Items with a created_on date of Today - easy. Now I want to Save this search to the TOC so users can run it daily - not so easy. I am trying to search for "Yesterday" in a date field, using AML. this: <Item type="Part" action="get" page="1" pagesize="200" maxRecords="" returnMode="itemsOnly"> <created_on condition="gt">yesterday</created_on> <created_on condition="lt">tomorrow</created_on> <generation condition="ge">1</generation> </Item> In my OnBeforeGet, I find the value "Yesterday" and substitute it for yesterday's date. this: <Item type="Part" action="get" page="1" pagesize="200" maxRecords="" returnMode="itemsOnly"> <created_on condition="gt">2019-05-02T00:00:00</created_on> <created_on condition="lt">2019-05-02T23:59:59</created_on> <generation condition="ge">1</generation> </Item> If I return this, then I get the error: <SOAP-ENV:Envelope> -<SOAP-ENV:Body> -<SOAP-ENV:Fault> <faultcode>SOAP-ENV:Server</faultcode> <faultstring>String was not recognized as a valid DateTime.</faultstring> -<detail> <af:legacy_detail>String was not recognized as a valid DateTime.</af:legacy_detail> <af:exception message =" String was not recognized as a valid DateTime. " type =" System.FormatException " /> </detail> </SOAP-ENV:Fault> </SOAP-ENV:Body> </SOAP-ENV:Envelope> 1) Can anyone explain how the system checks if the DateTime value is valid? 2) Is there an elegant, reusable way to perform this functionality? I have created a new Item, using an OnGet method to return todays parts, but this is a lot of effort for a relatively simple requirement. It is not reusable and it does not address why the above approach does not work. Many thanks, Martin.
