Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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?
0
David11011
Asked:
David11011
  • 7
  • 5
  • 2
  • +2
1 Solution
 
Patrick MatthewsCommented:
Try:

WHERE CAST(ORIGDATE AS DATE) > CAST('2011-01-01' AS DATE)
0
 
David11011Author Commented:
I'm getting the same problem
0
 
CrashmanCommented:
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/

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
TempDBACommented:
This depends upon how the optimizer takes the query. The query looks clean and shouldn't have any problem but still I want to recheck. So, can you check with the following way?
         
      SELECT  CUSTNO, ORIGDATE
       into #tmp_tbl
      FROM CUSTOMERS
      WHERE ISDATE(ORIGDATE) = 1

SELECT      CUSTNO, CAST(ORIGDATE AS DATE)
FROM #tmp_tbl
WHERE CAST(ORIGDATE AS DATE) > '2011-01-01'
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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

0
 
CrashmanCommented:
Many times i see this error, is the format of the date in the table and where condition
0
 
David11011Author Commented:
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?
0
 
CrashmanCommented:
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'
0
 
David11011Author Commented:
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
0
 
TempDBACommented:
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.
0
 
CrashmanCommented:
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
0
 
CrashmanCommented:
if you get error you have data in the view and this data is not date...
0
 
David11011Author Commented:
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.
0
 
CrashmanCommented:
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...

0
 
David11011Author Commented:
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.
0
 
CrashmanCommented:
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)
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now