nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

2660624

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

Find more posts tagged with

Accepted answers

All comments

Robert Angel

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 Acha

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

2660624

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

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

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

Robert Angel

"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

Datatype is TIMESTAMP when I import it.

Robert Angel

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

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

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

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!!