CODEWITHSUNDEEP
sql-serversql-server-2012
CPMunich
CPMunich

Reputation: 725

sql server 2012 Conversion failed when converting date and/or time from character string

I know there are many posts about the error message "Conversion failed when converting date and/or time from character string" but I couldn't find out why I get the error.

I have a table (let's call it MyTable) that has a varchar(255) column Filename. Every filename has a date portion in it. For example:

[Filename]
-----------------
FILE-160105_1.CSV
FILE-160105_2.CSV
FILE-160106_1.CSV
FILE-160106_2.CSV
...

My task is to get the date part out of the filenames and filter the resultset to a specific range of dates. The first step works fine

  SELECT Convert(date, SUBSTRING([Filename],6,6), 12) AS dat
  FROM mytable

and I get a correct result without errors:

dat
----------
05.01.2016
05.01.2016
06.01.2016
06.01.2016

But now, when I put the SELECT-Statement above in a common table expression and filter the dat column, I get the error as shown. The complete code which queries the base table in the database is as follows:

WITH CTE AS
(
  SELECT Convert(date, SUBSTRING([Filename],6,6), 12) AS dat
  FROM dbo.myTable
)
SELECT dat
FROM CTE
WHERE dat = Convert(date,'160105',12);

What I do not understand is, why the SELECT in the CTE gives me a result set, converted as date but the outer query tries to convert something.

Because I could not find the error, I thought, I could use another CTE to generate the same data as in MyTable for testing. And the funny part is: The following example works, but when I query the basetable directly, an error is thrown. Why?

WITH mytable as
(
  SELECT [Filename] FROM (VALUES ('FILE-160105_1.CSV'), ('FILE-160105_2.CSV'), ('FILE-160106_1.CSV'),
                                ('FILE-160106_2.CSV')) AS Files([Filename])
)
, CTE AS
(
  SELECT Convert(date, SUBSTRING([Filename],6,6), 12) AS dat
  FROM mytable
)
SELECT dat
FROM CTE
WHERE dat = Convert(date,'160105',12);

This gives me the correct result:

dat
---------
05.01.2016
05.01.2016

It would be great if someone could help me. Thanks. PS: By the way: the session language in SQL-Server is set to us_english

******** UPDATE ******

In the comments there is an advice to use TRY_CAST. When I run the following code, SQL Server Returns no error. I get the result with all rows:

WITH CTE AS
(
  SELECT Convert(date, SUBSTRING([Filename],6,6), 12) AS dat
  FROM dbo.MyTable
  WHERE [Filename] LIKE 'FILE-%'
)
SELECT dat, TRY_CAST(dat as date) as trydat
FROM CTE

When I look through the rows manually there are no NULLs in the column trydat so the CAST was successful. BUT, when I put TRY_CAST in the WHERE Clause to filter the dat column the Statement Fails:

WITH CTE AS
(
  SELECT Convert(date, SUBSTRING([Filename],6,6), 12) AS dat
  FROM dbo.MyTable
  WHERE [Filename] LIKE 'FILE-%'
)
SELECT dat
FROM CTE
WHERE TRY_CAST(dat as date) = '20160106';

I think there is something wrong with the table. When I create a new table and query it, there are no conversion problems.

Upvotes: 0

Views: 978

Answers (1)

CPMunich
CPMunich

Reputation: 725

The hint from Matt Gibsons comment gave me the idea to re-write the query and use the APPLY-Operator instead.

The query works now with the SQL-Statement

SELECT [Filename], A1.dat
FROM dbo.MyTable
OUTER APPLY (SELECT Convert(date, SUBSTRING([Filename],6,6), 12)) AS A1(dat)
WHERE [Filename] LIKE 'FILE-%'
  AND A1.dat = '20160120'

I think for the original-query the optimizer decides to first CAST AS date and then filter the rows with [Filename] LIKE 'FILE-%'.

This doesn't work because in the table are different filenames where the date is at different positions. But those I didn't want to query.

Upvotes: 1

Related Questions