1

I am reading data from data lake (csv) and when running the below query, I am getting a 'Conversion failed when converting date and/or time from character string' error message.

    select convert(datetime, NullIf(ltrim(rtrim([Date started])), ''), 111)
    FROM dl.temp

Looked through the data and checked the source file as well, couldn't spot anything unusual.

As soon as I include the * and change the query to the below everything runs fine and the conversion seem to be doing its job. 

    select convert(datetime, NullIf(ltrim(rtrim([Date started])), ''), 111),*
    from dl.temp

Out of curiosity also wanted to check the max and minimum date, so running max gives me the following:

enter image description here

However when I search for that particular value like below, I don't get any rows returned. It seems like it setting it to the column name. Does anyone know what is going on?

    select *
    from dl.temp
    where [Date started] = 'Date started'

I am running this against an Azure Data Warehouse.

Sharingan
  • 333
  • 5
  • 19
  • 2
    just looks like your csv might have a header row and you're importing that as well... try `select * from dl.temp where [Date started] like '%Date started%'` – JamieD77 Mar 02 '18 at 16:41
  • Try running the following: `SELECT [Date started] FROM dl.temp WHERE TRY_CONVERT(date,[Date started]) IS NULL;`. – Thom A Mar 02 '18 at 16:41
  • @JamieD77 already tried it, doesn't return any row – Sharingan Mar 02 '18 at 16:49
  • @Larnu the TRY_CONVERT function is not available in Azure Databases yet – Sharingan Mar 02 '18 at 16:50
  • (Wrong link before). It is in Azure SQL Databas, not in Azure SQL Data Warehouse. At least according to the article: [`TRY_CONVERT`](https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql). If you're using Azure SQL Data Warehouse, rather than Azure SQL Database, could you please update your tags accordingly? :) – Thom A Mar 02 '18 at 16:54
  • 1
    @Larnu, updated tags. I am using Azure SQL Data Warehouse to be clear – Sharingan Mar 02 '18 at 17:11
  • Is `dl.temp` an external table? Does the file have a header row? – GregGalloway Mar 03 '18 at 02:46
  • @GregGalloway, yes it is an external table. It does have a header row and first row gets rejected – Sharingan Mar 03 '18 at 09:46
  • @Sharingan but your max() screenshot contradicts that statement. Are you load multiple files? Can you provide the `CREATE EXTERNAL TABLE` statement? – GregGalloway Mar 03 '18 at 13:07

1 Answers1

0

I think you'll find the issue is in your external file format.

In the CREATE EXTERNAL FILE FORMAT you probably need to add FIRST_ROW=2 in your FORMAT OPTIONS.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql

Ron Dunn
  • 2,971
  • 20
  • 27