Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Date filter having sql server as data source not working in obiee 11g

Received Response
22
Views
11
Comments
2660624
2660624 Rank 3 - Community Apprentice
edited Aug 13, 2024 5:45PM in Oracle Analytics Cloud and Server

Hi,

I am using sql server as data source in obiee 11g but when I am using any date field in filters in reports its giving error as :

[ODBC SQL Server Driver][SQL Server]'TO_DATE' is not a recognized built-in function name.

I checked the physical query and found that to_date is applied in where clause. The report is working without date filter and report is having date column.

Can anybody suggest how I can fix this issue in OBIEE.

Thank You

«1

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    What odbc connector are you using to connect to your physical data source?

    When you say checked the physical query do you mean without any other logic being applied in the analysis?

  • Joel
    Joel Rank 8 - Analytics Strategist

    what is the exact column formula of your date field? Is the field defined as a DATE datatype in the repository?

  • 2660624
    2660624 Rank 3 - Community Apprentice

    Its ODBC 3.5 as call interface and driver as sql server.

    You were right I am getting to_date without applying any logic.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Can you try connecting with; -

    "Data Direct ODBC, set of drives included with OBIEE, which enable connection to SQL Server, MySQL, Hive etc –The 6.0 data drivers are provided by Oracle and are installed with OBIEE."

  • 2660624
    2660624 Rank 3 - Community Apprentice

    Its simple date column whose datatype is TIMESTAMP. I  tried with DATETIME too but doesn't solve the issue.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    "I tried with datetime too" - what is the data type of the column in sqlserver?

    When you use the wizard to import the table what data type does OBIEE assign to that column?

  • 2660624
    2660624 Rank 3 - Community Apprentice

    Datatype is  TIMESTAMP when I import it.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Ok, I would not change it from that, but it does look like a problem with your underlying driver, can you try my suggestion?

  • 2660624
    2660624 Rank 3 - Community Apprentice

    I tried with Data Direct 6.0 SQL Server wire protocol. But getting same issue as:

    [DataDirect][ODBC SQL Server Driver][SQL Server]'TO_DATE' is not a recognized built-in function name.. (HY000)

    State: HY000. Code: 16002. [nQSError: 16002] Cannot obtain number of columns for the query result. (HY000)

    SQL Issued: SELECT DISTINCT "Permit Fact"."Received Date" saw_0, "Site Dim"."Site Name" saw_1 FROM "Wellview_Siteview" WHERE "Permit Fact"."Received Date" >= timestamp '2008-03-08 00:00:00' ORDER BY saw_0, saw_1. 
    In filter I am giving as

    pastedImage_0.png
    Problem is obiee is trying to convert in this format

    T835376.DTTMRECIEVED >= TO_DATE('2008-03-08 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') )

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Could this be your issue =>

    https://community.oracle.com/thread/1038714

    Note also that 11g is not supported with later versions of sqlserver, you are really past the point of needing to upgrade!!