Skip to main content
Question

Date Format for MSSQL Linked Server Query

  • March 6, 2024
  • 4 replies
  • 2 views
M

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

    4 replies

    Brian Cram
    Forum|alt.badge.img+3
    • Brian CramRocketeer
    • Rocketeer
    • March 20, 2024

    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.

    M
    • Marcus Rhodes
    • Participating Frequently
    • March 20, 2024

    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]"))

    Brian Cram
    Forum|alt.badge.img+3
    • Brian CramRocketeer
    • Rocketeer
    • March 20, 2024

    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. 

    STEFANO MARAN
    • STEFANO MARAN
    • Participating Frequently
    • March 21, 2024

    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

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement