I am trying to import data from MSSQL to HDFS using free form query import. I would like to extract DATE from unix time in the table. I tried the below command and it gives me a weird output and not even sure why that's the output.
sqoop import --connect "jdbc:sqlserver://xxx.xx.xxx.xx:xxxx;databaseName=Projector" --username USER -P --query 'select convert(varchar,DATEADD(ss,projectorunixtime,'1970-01-01'),112) as dt from TABLENAME where (projectorunixtime='1511963773') AND $CONDITIONS' --fields-terminated-by '\t' --m 1 --target-dir /tmp/dateconverted
The output should be something like :
-----dt-----
20171129
Instead, output looks like
-----dt-----
19530420
The below query gives me the desired result in mssql console and also when I tried it running via Spark sql.
select convert(varchar,DATEADD(ss,projectorunixtime,'1970-01-01'),112)
Could anyone suggest me a way to work this around?