CODEWITHSUNDEEP
sqlsql-server
Irene Hu
Irene Hu

Reputation: 21

Filter Data in SQL Server

I have a table like below :

[TMP_TBL] 
(
    ... 
    [DT_REFUND] [varchar(20)] NULL,
    [MSG_PROCESS] [nvarchar(max)] NULL,
    ... 
)

and it contains data like this:

| DT_REFUND | MSG_PROCESS    |
| A-BC---D  | WRONG DATETYPE |
| 20180301  | NULL           |
| 20180331  | NULL           |

When I do a query like:

select * 
from TMP_TBL 
where isnull(MSG_PROCESS, '') = ''

everything goes fine.

But when I do query like:

select * 
from TMP_TBL 
where isnull(MSG_PROCESS, '') = ''
  and convert(DATE, DT_REFUND) >= convert(DATE, '20180301');

I get an error:

Conversion failed when converting date and/or time from character string

Why did this happen, and how to fix it?

Thanks

Upvotes: 1

Views: 80

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You just need to filter out null values and compared date via cast() or convert() function 

select * 
from TMP_TBL 
where MSG_PROCESS is null and
      cast(DT_REFUND as date) >= '20180301';

This assumes null value has always date as your sample data suggests

In other way, first you could check DT_REFUND has some kind of date format data via TRY_CONVERT() function. So, your where clause should do

where MSG_PROCESS is null and TRY_CONVERT(date, DT_REFUND) is not null and
          cast(DT_REFUND as date) >= '20180301';

However, the above has some redundant filtration before cast so you could also express that as 

where MSG_PROCESS is null and TRY_CONVERT(date, DT_REFUND) >=  '20180301';

Upvotes: 0

PSK
PSK

Reputation: 17943

Ideally you should have avoided using VARCHAR data type for dates.

For the current scenario, you can use TRY_CONVERT like following.

select * 
from TMP_TBL 
where isnull(MSG_PROCESS, '') = ''
and TRY_CONVERT(DATE, DT_REFUND) >= convert(DATE, '20180301');

As your date column is VARCHAR, so there is a possibility of having empty spaces also, to handle this you can try RTRIM function before converting to date like following.

TRY_CONVERT(DATE, RTRIM(DT_REFUND)) >= convert(DATE, '20180301');

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

First you check the value in the column is DATE format or other format, using ISDATE function. And compare with the date value.

If it is not in date format it returns NULL value.

Try this:

SELECT * 
FROM TMP_TBL 
WHERE ISNULL(MSG_PROCESS,'') = '' 
    AND CONVERT(DATE, CASE WHEN ISDATE(DT_REFUND)=1 THEN DT_REFUND ELSE NULL END) >= CONVERT(DATE, '20180301');

Upvotes: 0

Related Questions