CODEWITHSUNDEEP
sql-servert-sql
Cristian Contreras
Cristian Contreras

Reputation: 113

Date column won't convert if I use it in the where clause

So I have this code that runs great:

SELECT 
  QuoteDet.QuoteNo, 
  Quote.SalesID, 
  Quote.CustCode, 
  QuoteDet.PartNo AS [Version], 
  QuoteDet.Price1 AS [Unit Price], 
  CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1) AS [Submittal Date]  
FROM QuoteDet INNER JOIN Quote ON QuoteDet.QuoteNo = Quote.QuoteNo
WHERE QuoteDet.Status IN ('Req', 'Pend')
AND QuoteDet.User_Text1 <> 'HOLD'
GROUP BY QuoteDet.QuoteNo, Quote.SalesID, Quote.CustCode, QuoteDet.PartNo, 
QuoteDet.Price1, QuoteDet.User_Text1
ORDER BY CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1)

But when I add the following condition in the where clause to get the current week, I get the message "Conversion failed when converting date and or time from character string

SELECT 
  QuoteDet.QuoteNo, 
  Quote.SalesID, 
  Quote.CustCode, 
  QuoteDet.PartNo AS [Version], 
  QuoteDet.Price1 AS [Unit Price], 
  CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1) AS [Submittal Date]  
FROM QuoteDet INNER JOIN Quote ON QuoteDet.QuoteNo = Quote.QuoteNo
WHERE QuoteDet.Status IN ('Req', 'Pend') 
AND CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1) >= 
DATEADD(DAY, 1-DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE()))
AND CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1) <  
DATEADD(DAY, 8-DATEPART(dw, GETDATE()), CONVERT(DATE, GETDATE()))
AND QuoteDet.User_Text1 <> 'HOLD'
GROUP BY QuoteDet.QuoteNo, Quote.SalesID, Quote.CustCode, QuoteDet.PartNo, 
QuoteDet.Price1, QuoteDet.User_Text1
ORDER BY CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1)

Why don't I get the error in the first query? It's also converting to a date, yet no error

Upvotes: 0

Views: 72

Answers (2)

Shannon Severance
Shannon Severance

Reputation: 18410

Another possibility, first query works, because the conversion in the select clause happens after the narrowing of rows by the where clause. By moving the convert to the where clause it can now be run on all rows, some of which may have bad data that does not make it through to the select clause in the first query.

If you are on a sufficiently new version of SQL Server, TRY_PARSE will allow conversions without errors by returning NULL for un-convertable strings.

Upvotes: 1

Clive Ciappara
Clive Ciappara

Reputation: 558

In your first query you are:

  • first casting the 'QuoteDet.User_Text1' as a Date
  • then converting the result to a VARCHAR of length 30, resulting in a string

In your second query, you are trying to use a string to check for a date. What you should do in the WHERE clause is to remove the 'Convert' method call, and just use the 'Cast' method. So instead of this:

    CONVERT(VARCHAR(30), CAST(QuoteDet.User_Text1 AS DATE), 1)

you have to use the following in your WHERE clause:

    CAST(QuoteDet.User_Text1 AS DATE)

Upvotes: 1

Related Questions