D3 and mvBase

 View Only

 Date format error in MSSQL Linked Server to D3

Michael Boucher's profile image
Michael Boucher posted 03-06-2024 21:20

Hi all,

Apologies if I am in completely the wrong place!

We have an application running on D3 and have a linked server set up in our MSSQL server using the D3 ODBC. When selecting a date column I'm getting the below error. Google tells me I need to convert it to a string somehow, but I'm not sure of the functions in D3 to achieve this. Any help would be much appreciated!

Error converting data type DBTYPE_DBDATE to date.

select * from openquery (LINKEDSERVER, 'SELECT DATE_COMPL FROM SOH SOH WHERE (SOH.DATE_COMPL={d ''2024-03-01''})')

Many thanks,
Michael

David Knight's profile image
PARTNER David Knight posted 03-07-2024 04:07

Hi,

A lot depends on what d3 is emitting via it's SQL interface. I forget the specific syntax in d3 [I can find it if really needs-be] but there is a command which updates the sql interface 'definition' table. I think it is SQL-TABLE or somesuch. In that one defines the data elements to be exposed and unless over-ridden specifically in the command will be 'exposed' as a 'TYPE' according to the d3 dictionary for element. So my guess there is a field SOH.DATE in the file COMPL.

Who maintains the d3 side? Speak to them and find out what is 'in' the SQL-TABLE for this file and field. One can 'force' a TYPE by using a modifier against that definition in the command.

HTH

Chris Macadam's profile image
ROCKETEER Chris Macadam posted 03-07-2024 13:40

Hi Michael,

If, when you execute the following command

sql-display-table soh  (t

it shows the DATA-TYPE of the DATE_COMPL column to be a DATE type (which I'm assuming it will, otherwise you probably want to fix that), you might want to check the actual data stored in that attribute of the SOH file to see if there might be some data corruption.  If there is data corruption, it's possible that an invalid date is being returned and causing the problem.

Best regards.

Michael Boucher's profile image
Michael Boucher posted 03-07-2024 15:33

Hi Chris, where would I run that command? I don't have direct access to the D3 system. Is there a way to convert it to a string so I can pull it into SQL and check it there?

Chris Macadam's profile image
ROCKETEER Chris Macadam posted 03-07-2024 15:54

Michael,

That command would be executed from TCL within D3.  I don't know of a way to get the information using an SQL query because the D3 SQL tables themselves are not 'normalized' so the entry for each D3 file that has been SQL'ized contains multivalues which SQL doesn't understand.

Chris