D3 and mvBase

 View Only

 Date Format for MSSQL Linked Server Query

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

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

Brian Cram's profile image
ROCKETEER Brian Cram posted 03-20-2024 10:01

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.

M Arcus1's profile image
M Arcus1 posted 03-20-2024 10:11

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

Brian Cram's profile image
ROCKETEER Brian Cram posted 03-20-2024 10:18

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

Stefano Maran's profile image
PARTNER Stefano Maran posted 03-21-2024 07:38

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