brainpow3r opened a new issue, #2846:
URL: https://github.com/apache/drill/issues/2846
**Describe the bug**
We have an Apache Drill instance set up and running in embedded mode. RDBMS
plug-in is used to connect existing MSSQL database to Drill. Plug-in
configuration looks like this
`{
"type": "jdbc",
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"url": "jdbc:sqlserver://*********;databaseName=*******",
"username": "MyUserName",
"password": "MyPassword",
"sourceParameters": {
"keepaliveTime": 0,
"minimumIdle": 0,
"idleTimeout": 3600000,
"maximumPoolSize": 10,
"maxLifetime": 21600000
},
"authMode": "SHARED_USER",
"writerBatchSize": 10000,
"enabled": true
}`
Most of the statements we tried running through Drill works fine. But
recently we wrote a couple of queries where we need to extract month from a
**date** field and we encountered some errors. More precisely:
When executing following SQL statement
`select YEAR(s.DateColumn) from rdbms_schema.MyTable`
We receive such error response from Apache Drill
`
org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The
JDBC storage plugin failed while trying setup the SQL query.
Sql: SELECT EXTRACT(YEAR FROM "DateColumn")
FROM "rdbms_schema"."MyTable"
Fragment: 0:0
`
From which we can see that Drill actually translates our query before
passing to MSSQL server to use EXTRACT(...), but the problem is that EXTRACT
keyword is not supported in MSSQL server.
**To Reproduce**
Steps to reproduce the behavior:
1. Set up Apache Drill instance in embedded mode and an MSSQL Server
instance.
2. Create a database and a table inside it with _date/datetime/datetime2_
column.
3. Add RDBMS plug-in to your Apache Drill instance and configure it to
connect to your MSSQL Server instance.
4. Try executing a query which extracts _year/month/day_ from
_date/datetime/datetime2_ column. Something along the lines of `SELECT
MONTH([DateColumn]) FROM rdbms_schema.MyTable`
5. See error.
**Expected behavior**
Query should be translated to support underlying RDBMS datasource keywords
and functions.
**Error detail, log output or screenshots**
Prefer character data over screenshots for error messages and log output.
**Drill version**
1.21.1
**Additional context**
As a workaround we tried getting the month field by using FORMAT like this
(equivalent works in MSSQL server):
`select FORMAT(`DateColumn`, 'MM') from rdbms_schema.MyTable`
But we received the following error:
`
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
From line 1, column 8 to line 1, column 27: No match found for function
signature FORMAT(<DATE>, <CHARACTER>)
`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: dev-unsubscr...@drill.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
