Change Sqoop's Date Format in Incremental Import from SQL Server

Question

TL;DR - Is it possible to change the date format Sqoop uses?

I am importing data from a SQL Server using Sqoop (version 1.4.6) and am specifying a datetime column as my --check-column.

Sqoop is querying the database using dates in the format yyyy-MM-ddd hh:mm:ss.SSS. However, since SQL server is configured to use British dmy date format, it, counterintuitively, interprets a date beginning with a year as having the day in the second position rather than the month (wtf?!).

E.g SELECT ... WHERE modified < '2017-01-31 00:00:00.000' is interpreted as selecting the data where modified is less than the 1st day of the 31st month 2017 which obviously throws an error.

Is it possible to change the date format Sqoop uses?

Answer 1

A quick scan of the Sqoop documentation did not reveal a way to change the date format. The Microsoft Support article here suggests that you could avoid the problem by issuing a

SET DATEFORMAT 'ymd'

statement immediately after opening the connection, but it appears that Sqoop only supports that sort of operation for Oracle (oraoop-site-template.xml) and not for other JDBC drivers.

There is an mssql-jdbc pull request that, if accepted, would let you add ;connectionDateformat=ymd to your Sqoop connection URL. In the meantime you may just have to change the default language of the SQL login for your Sqoop job from "British English" to (US) "English":