CODEWITHSUNDEEP
sqlsql-server-2008
Steve_m
Steve_m

Reputation: 53

SQL server date convert giving error

I have the following query:

select A.ACCOUNT_NUM,
       convert(date, B.EFFECTIVE_DATE, 112) as 'EFFECTIVE_DATE',
       B.INTEREST_RATE
from Table1 A
left join Table2 B
on A.ACCOUNT_NUM = B.ACCOUNT_NUM

ACCOUNT_NUM is varchar(12) in both tables, EFFECTIVE_DATE is varchar(8) and in the format '20131018' This is giving me a 'Conversion failed when converting date and/or time string'. If I run the query without the convert it runs fine.

However I've checked the contents of EFFECTIVE_DATE using ISDATE() and there are no issues there. Also, if I do the following:

select A.ACCOUNT_NUM,
       B.EFFECTIVE_DATE,
       B.INTEREST_RATE
into #temp
from Table1 A
left join Table2 B
on A.ACCOUNT_NUM = B.ACCOUNT_NUM


select convert(date, EFFECTIVE_DATE, 112)
from #temp

it correctly converts the column into date format with no errors. What am I missing please, it's driving me crazy and I'm sure it's something very simple!

Thanks

Upvotes: 2

Views: 396

Answers (2)

Kaf
Kaf

Reputation: 33839

These sort of problems can happen when you store DATE in STRING type fields. There may be one or two strings which are NOT convertible as date in your column. Try this with CASE and see if you get the data. 

SELECT A.ACCOUNT_NUM,
       CASE WHEN ISDATE(B.EFFECTIVE_DATE) THEN CONVERT(DATE, B.EFFECTIVE_DATE, 112) 
            ELSE CONVERT(DATE, '20000101', 112) END AS 'EFFECTIVE_DATE',
       B.INTEREST_RATE
FROM Table1 A
LEFT JOIN Table2 B ON A.ACCOUNT_NUM = B.ACCOUNT_NUM

If you get any EFFECTIVE_DATE values as 2000-01-01, that means you have to sort out those string values.

Also, you can try below query to find out which rows are causing the issue:

SELECT B.ACCOUNT_NUM, B.EFFECTIVE_DATE
FROM Table2 B
WHERE ISDATE(B.EFFECTIVE_DATE) = 0

Upvotes: 1

Chris Pickford
Chris Pickford

Reputation: 9001

I just recreated your schema and ran your query and it worked fine. Without doubt the issue is a badly formatted string in your Table2.EFFECTIVE_DATE column.

Upvotes: 0

Related Questions