Skip to main content

Hi all,

Apologies if I'm in completely the wrong place!

We have an appplication running on D3 and have a linked sever in MSSQL using the ODBC connection.

When retrieving a date column I am getting error : Error converting data type DBTYPE_DBDATE to date.

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

From Googling it seems I need to convert it to a string, but I am unsure of the conversion functions in D3. Any assistance would be much appreciated!

Many thanks,
Michael

Hi all,

Apologies if I'm in completely the wrong place!

We have an appplication running on D3 and have a linked sever in MSSQL using the ODBC connection.

When retrieving a date column I am getting error : Error converting data type DBTYPE_DBDATE to date.

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

From Googling it seems I need to convert it to a string, but I am unsure of the conversion functions in D3. Any assistance would be much appreciated!

Many thanks,
Michael

Unfortunately there is no native ICONV/OCONV for a YYYY-MM-DD date format. I've always written a subroutine to extract the 4-digit year, 2-digit month and 2-digit day, then concatenate them with hypens.

Hi all,

Apologies if I'm in completely the wrong place!

We have an appplication running on D3 and have a linked sever in MSSQL using the ODBC connection.

When retrieving a date column I am getting error : Error converting data type DBTYPE_DBDATE to date.

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

From Googling it seems I need to convert it to a string, but I am unsure of the conversion functions in D3. Any assistance would be much appreciated!

Many thanks,
Michael

Try ... CONVERT(" ",",",OCONV(DATE(),"DYMD[4,2,2]"))

Hi all,

Apologies if I'm in completely the wrong place!

We have an appplication running on D3 and have a linked sever in MSSQL using the ODBC connection.

When retrieving a date column I am getting error : Error converting data type DBTYPE_DBDATE to date.

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

From Googling it seems I need to convert it to a string, but I am unsure of the conversion functions in D3. Any assistance would be much appreciated!

Many thanks,
Michael

Tried that OCONV(date(),"DYMD[4,2,2]") and it just gave me the year. 

Hi all,

Apologies if I'm in completely the wrong place!

We have an appplication running on D3 and have a linked sever in MSSQL using the ODBC connection.

When retrieving a date column I am getting error : Error converting data type DBTYPE_DBDATE to date.

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

From Googling it seems I need to convert it to a string, but I am unsure of the conversion functions in D3. Any assistance would be much appreciated!

Many thanks,
Michael

In the past "D4-Y" was "announced" as a conversione expression for that:  no yet arrived.

I actually use for minimal OCONV() and mininal string concatenation:

d = OCONV( DATE(), "D4-U" )  ;* d is MM-DD-YYYY regardless D3 date format (STD vs EUR)

d = d[7,4] : "-" : d[1,5] ;* MM-DD-YYYY -> YYYY-MM-DD

Terms & ConditionsAccessibility statement