Date format for ODBC

Hello, I use ODBC to put data into NA 3.10, but could not understand what date format it accepts? I tried dd/mm/yyyy, mm/dd/yyyy, yyyy-mm-dd, dd.mm.yyyy, but without success. IOW how to write SQL like this: insert into “Competitor Price” ( …, “Date”, …) values (…, ‘15.01.2003’, …)?

Hi. The ODBC date format is quite peculiar. Try: {d’DD-MM-YYYY’} Where DD is day, MM month and YYYY four-digit year. So your SQL string would be: "Insert … values(…,’{d’16-01-2003’}’,…) Regards.

Thanks, but it does not work. I suspect that {d’…’} format is some kind of Access(?) macros. I succeed in sending data as parameter from Delphi, like this: Nav.SQL:=‘insert … values(…,:Date,…)’ Nav.ParamByName(‘Date’).AsDateTime:=Trunc(Var_of_type_TDateTime); but failed to send plain text SQL without using parameters.

Hi. The date format is not any kind of macro. That’s the format you have to use in C/ODBC. I use that format in my tiny web application built in ASP to insert/view/delete/update data in Navision. I don’t know how you’re trying to send using plain SQL. I use plain SQL in ASP and I’ve send dates many times. It’s like this: Set Conn = Server.CreateObject(“ADODB.Connection”) my_DSN = “DSN=Navision;…” Conn.open my_DSN mysql = “INSERT into …” Conn.Execute mySQL Conn.Close set Conn=nothing Are you using C/ODBC provided by the CD? In what environment/programming language are you attempting to do this? Regards

Not sure if this will help or not, but here’s a portion of sql code using DTML and C/ODBC: FROM “Sales Shipment Header”, “Shipment Method” WHERE (“Bill-to Customer No_” LIKE ‘’ AND “Posting Date” >= {d} AND “Posting Date” <= {d})AND (“Shipment Method”.Code = “Sales Shipment Header”.“Shipment Method Code”) ORDER BY “No_” DESC Sample values: 2002-06-01 2002-07-31

Keep in mind that ADO isn’t officially supported by Navision through C/ODBC. I had an application that worked file with 3.01B and wouldn’t work with a 3.10B database. Not everyone will have that experience but it’s something to consider. DjangMan

quote:


Originally posted by BFernandes
The date format is not any kind of macro. That’s the format you have to use in C/ODBC. I use that format in my tiny web application built in ASP to insert/view/delete/update data in Navision. I don’t know how you’re trying to send using plain SQL. Are you using C/ODBC provided by the CD? In what environment/programming language are you attempting to do this?


I tried to use Borland’s SQL Explorer, that comes with Delphi, and of cource C/ODBC from CD. I tried this: select * from “Preaction” where “Date”=’{d’08-01-2003’}’, that results in error “Unexpected extra token: 08”, this variant: …where “Date”=’{d’‘08-01-2003’’}’ results in “Invalid operand for operator: =”

Hi Konstbel. Try to put the year-month-day format. I tried this: SELECT * from Purchase_Order WHERE Order_Date <= {d’2003-01-01’} and it works. Try it yourself. Regards

Just wanted to say thanks for the info regarding the dates issue. I have been desperatly trying to find a way of getting this to work, I too have been getting “Invalid operand for operator: =>” even when using the BETWEEN command. I’ve just used the solution you posted and it works fine, thank you again.

where “Date”={d’08-01-2003’} AND not where “Date”=’{d’08-01-2003’}’ date is a special format, not a formated string

Although I still get the Invalid Operand if I try perform a query on dates but with a join, having a lot of trouble using joins, lots of lexical elements not found, especially if I try to use group by and sum??

I think this is the most common question on this forum! This ‘strange’ date format comes from the ODBC specification - C/ODBC can’t be blamed for it, unless of course it doesn’t handle it properly. From the ODBC Programmers Reference: ============================================================ Date, Time, and Timestamp Literals The escape sequence for date, time, and timestamp literals is {literal-type ‘value’} where literal-type is one of the values listed in the following table. literal-type Meaning Format of value d Date yyyy-mm-dd t Time* hh:mm:ss[1] ts Timestamp yyyy-mm-dd hh:mm:ss[.f...][1] [1] The number of digits to the right of the decimal point in a time or timestamp interval literal containing a seconds component is dependent on the seconds precision, as contained in the SQL_DESC_PRECISION descriptor field. (For more information, see SQLSetDescField.) For more information about the date, time, and timestamp escape sequences, see “Date, Time, and Timestamp Escape Sequences” in Appendix C: SQL Grammar. For example, both of the following SQL statements update the open date of sales order 1023 in the Orders table. The first statement uses the escape sequence syntax. The second statement uses the Oracle Rdb native syntax for the DATE column and is not interoperable. UPDATE Orders SET OpenDate={d ‘1995-01-15’} WHERE OrderID=1023 UPDATE Orders SET OpenDate=‘15-Jan-1995’ WHERE OrderID=1023 etc etc ============================================================

quote:


Originally posted by robertc
This ‘strange’ date format comes from the ODBC specification - C/ODBC can’t be blamed for it, unless of course it doesn’t handle it properly. {skip} UPDATE Orders SET OpenDate={d ‘1995-01-15’} WHERE OrderID=1023 {skip}


Thats all sounds fine. But it still DOES NOT work from Delphi [:(] ending with error: “Invalid operand for operator: =” The only way I found, was use of parametrized query. May be Borland realisation of ODBC is wrong? (strange assumption, isn’t it?) What clients are succeeded in transfering dates? As I understand - ADO over ODBC works (not checked either), what else?

Hello konstbel & the rest, I don’t know if the ODBC vs Date problem is solved for you guys or not. I have a solution that at least worked for me with NA 3.10. Uninstall any previous ODBC installations but make a copy of codbc.dll before uninstalling. Install ODBC 3.10A, of course, it won’t work with 3.10 client. So replace the new codbc.dll with the old (saved) codbc.dll. I hope that works for you as well. Tariq

I’m using ODBC driver in my php pages, and I’m discovering a lot syntiax and lexical rules. Anybody know how to use sub-queries? like “SELECT no_ from Item where no_ in (select …something)” I always get “Expected lexical element not found”

quote:


Originally posted by robertc
This ‘strange’ date format comes from the ODBC specification - C/ODBC can’t be blamed for it, unless of course it doesn’t handle it properly. {skip} UPDATE Orders SET OpenDate={d ‘1995-01-15’} WHERE OrderID=1023 {skip}


OK, finally I found that this escape sequence is some kind of Microsoft ODBC implementation, realised not in driver, but in client library part. The proof is quite simple - work with the same driver (for Interbase database) from different development tools: from MS Visual C and Delphi. From MSVC it supports the above date format, but does not supports UNION clause and some others, specifical for Interbase. From Delphi - another way, it supports all Interbase clauses, but does not supports Microsoft date format. So, one can conclude, that MSVC client library performs some parcing and sends query to ODBC driver in some different format, may be as query parameters. Blamed Microsoft!

Well, it is not merely a Microsoft ODBC implementation, but part of the specification itself, that Microsoft is responsible for making because ODBC is a Microsoft technology (allbeit originally a collaboration with Lotus and others). The specification (or a chosen level of it) must be adhered to by driver writers if they would like their driver to be fully interoperable. I don’t see that MS is to blame then, if ISVs don’t successfully do that. Sorry.

Just for the record :slight_smile: The ODBC date format is not {d’DD-MM-YYYY’} but {d ‘YYYY-MM-DD’} Where DD is day, MM month and YYYY four-digit year. -And your SQL string will then be: insert into “Competitor Price” ( …, “Date”, …) values (…, {d ‘2003-01-15’}, …)

Bump for Paul Smith…

Study the c/odbc guide from the CD well. Only certain sql commands are supported with certain syntax.

Your application should pass that particular restricted SQL to C/ODBC.

Joining tables in a query is a no go. Expected not found means you give the animal something he does not know for dinner.

So you can only give what is known. Rest to be performed with other animals. Not a c/odbc problem but an overall SQL problem.

There is a tool better then the guide on the CD to examine drivers capabilities.