CODEWITHSUNDEEP
sqlsql-server
Kinjal
Kinjal

Reputation: 21

Conversion failed when converting datetime from character string in where clause

I am trying to use following SQL statement but as soon as fire the query i am getting 'Conversion failed when converting datetime from character string.' error message.

i have tried converting fiscalperiod as well as dates in where clause to valid datetime but as soon as i use filter for datetime query fails.

If i store the same result in temp table and then use date filters on the temp table then i get the expected results.

Can you please check what wrong in below query?

WITH TEMP_LEDGERPERIOD ( DATAAREAID, PERIODSTART, PERIODEND, FISCALPERIOD )
AS 
(
    SELECT dataareaid, PERIODSTART, PERIODEND, CONVERT(DATETIME, '01-' + COMMENTARIES) AS FiscalPeriod
    FROM LEDGERPERIOD LPT WITH (NOLOCK)
    WHERE (COMMENTARIES NOT LIKE '%OPEN' and COMMENTARIES NOT LIKE '%CLOSE')
)

Select * 
from TEMP_LEDGERPERIOD
where FiscalPeriod BETWEEN '2016/01/01' AND '2016/02/01'

Upvotes: 0

Views: 750

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

When using dates, you should either use ISO standard date formats or be explicit about conversions. I have no idea what COMMENTARIES looks like, but "01-" at the beginning of a date seems an unlikely format.

In addition, SQL Server does not guarantee the order of evaluation of expressions, unless you use a CASE. So, the filtering in the WHERE clause may take place after the calculation of the new value. Fortunately, SQL Server 2012+ offers TRY_CONVERT().

If I assume the format of COMMENTARIES is "YYYY-MM", then you can go for:

WITH TEMP_LEDGERPERIOD ( DATAAREAID, PERIODSTART, PERIODEND, FISCALPERIOD ) AS (
      SELECT dataareaid, PERIODSTART, PERIODEND,
             TRY_CONVERT(DATETIME, COMMENTARIES + '-01') AS FiscalPeriod
      FROM LEDGERPERIOD LPT WITH (NOLOCK)
      WHERE COMMENTARIES NOT LIKE '%OPEN' and COMMENTARIES NOT LIKE '%CLOSE'
     )
Select * 
from TEMP_LEDGERPERIOD
where FiscalPeriod BETWEEN '2016-01-01' AND '2016-02-01';

Finally, do you realize that BETWEEN is inclusive, so this includes the first day of February.

Upvotes: 1

Related Questions