asked on

Casting a Date

I have the query that is frying my brain! I need to cast a CHAR(8) column into a DATE and I can't figure out why it is returning this error message: "Conversion failed when converting date and/or time from character string."

I use a nested select statement to only return the rows that contain a valid date, then I select from that and cast the results as DATE.

*This query works

SELECT      CUSTNO, CAST(ORIGDATE AS DATE)
FROM (
      SELECT CUSTNO, ORIGDATE
      FROM CUSTOMERS
      WHERE ISDATE(ORIGDATE) = 1
) TBL1

* And this query returns the error:

SELECT      CUSTNO, CAST(ORIGDATE AS DATE)
FROM (
      SELECT CUSTNO, ORIGDATE
      FROM CUSTOMERS
      WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE CAST(ORIGDATE AS DATE) > '2011-01-01'

The only difference between these is the WHERE clause. But I've proved that all the data in the column can be casted as DATE because it successfully converts to DATE in the SELECT clause. Why is it that when I filter with the WHERE clause it throws the error?

Patrick Matthews

Try:

WHERE CAST(ORIGDATE AS DATE) > CAST('2011-01-01' AS DATE)

David11011

ASKER

I'm getting the same problem

Haver Ramirez

Maybe you must see the result and use the result format to compare values

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

ASKER CERTIFIED SOLUTION

TempDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Exactly, you will have to separate the query and even then while you will not get any errors the WHERE clause will make the results unpredicatable.

Anthony Perkins

On second thoughts I would be more inclined to do it this way:

SELECT  CUSTNO,
        CAST(ORIGDATE AS [DATE]) [DATE]
INTO    #Temp
FROM    CUSTOMERS
WHERE   ISDATE(ORIGDATE) = 1

CREATE INDEX #IX_Temp ON #Temp ([DATE])

SELECT  CUSTNO,
        [DATE]
FROM    #Temp
WHERE   [DATE] > '2011-01-01'

Open in new window

Haver Ramirez

Many times i see this error, is the format of the date in the table and where condition

David11011

ASKER

Thanks for you help guys.
I don't really understand why your suggestion works and my original query doesn't. But throwing my results into a temp table then filtering by date works perfectly. Anyone know why I need the temp table?

Haver Ramirez

SELECT CUSTNO, CAST(ORIGDATE AS DATE)
FROM (
SELECT CUSTNO, ORIGDATE
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE CAST(ORIGDATE AS DATE) > '2011-01-01'

as I Say, you are using the cast to format the date and maybe use a different format to compare

is not posible compare
'2011-10-12'
'12/10/2011'

David11011

ASKER

Ok maybe I'm on to something.
CUSTOMERS is not a table. It is a view that I've created. Once i pull the data out of the view and into a table I can cast as date in the where clause. Is there casting limitations on views?

SELECT *
INTO #TEMP0
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1

SELECT * FROM #TEMP0
WHERE CAST(ORIGDATE AS DATE) > '2011-10-03'

DROP TABLE #TEMP0

TempDBA

No its not anything to do with casting on view. To get the detail analysis, you can check the query plan where you can see how the optimizer worked on processing your query, I mean first it took the data and then filtered it or first it filtered and then casted.
Here I doubted is first it is trying to cast the full data and then it is filtering your condition. To be sure, you can see the query plan.

Haver Ramirez

execute this

SELECT CUSTNO, CAST(ORIGDATE AS DATE)
FROM (
SELECT CUSTNO, ORIGDATE
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1

and see the date format, and try to filter with the same format

Haver Ramirez

if you get error you have data in the view and this data is not date...

David11011

ASKER

The query that you have provided does work. The only time that casting throws the error is when I try and cast inside the WHERE clause. That is what is blowing my mind.

I know that it is not a formatting issue because all of the records in the ORIGDATE column follow this format '20111008'. The column is a char(8) and all the data is entered by our front end application so there is no room for human error.

Haver Ramirez

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1

see the format
and try this

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE ORIGDATE > '2011-01-01'

or this

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE ORIGDATE > '20110101'

you dont need to cast the where because the value in the internal query is a date...

David11011

ASKER

The problem is that the date is actually a variable that is defined at the beginning of a query large enough to be published as a book.

This works

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE ORIGDATE > '20110101'

But this throws the error.

DECLARE @STARTDATE AS DATE
SET @STARTDATE = '2011-01-01'

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE ORIGDATE >= @STARTDATE

If I declare @STARTDATE as a CHAR(8) and set it to equal '20110101' the query works fine. But I'm worried that there will be inaccuracies when calculates multiple years and months because SQL doesn't know that it is supposed to be a date and will calculate it like an integer.

Haver Ramirez

SELECT convert(varchar, getdate(), 112)

DECLARE @STARTDATE AS DATE
SET @STARTDATE = '2011-01-01'
--you can format in the set 20110101
--or

SELECT CUSTNO, ORIGDATE
FROM (
SELECT CUSTNO, CAST(ORIGDATE as DATE)
FROM CUSTOMERS
WHERE ISDATE(ORIGDATE) = 1
) TBL1
WHERE ORIGDATE >= SELECT convert(varchar,@stardate,112)