CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Sai
Sai

Reputation: 59

How to select datetime where isnull value from SQL Server 2012?

I have tb_user_log to collect user login and logout history and it has two cols (timein and timeout) but if the system error the timeout col is null value.

I need to select it and display even is null or not null.

I have below code but just returns only where value is not null and it hide the recode where timeout is null:

select *    
from tb_user_log 
where cast(Logintime as date) >= '2018-05-16' 
  and (cast(Logouttime as date) <= '2018-05-16' or
       cast(logouttime as date) <= '') 
order by id desc

Is any incorrect point in my code? It does not return the null recode.

Or any better way to select it? My datatype is datetime and I am using C# + SQL Server 2012.

Thank you.

Upvotes: 1

Views: 154

Answers (1)

Andrea
Andrea

Reputation: 12355

You can test Logouttime against NULL:

select *    
from tb_user_log 
where cast(Logintime as date) >= '2018-05-16' 
  and (cast(Logouttime as date) <= '2018-05-16' or logouttime is null) 
order by id desc

Or you can handle NULL value in the condition:

select *    
from tb_user_log 
where cast(Logintime as date) >= '2018-05-16' 
  and (cast(isnull(Logouttime,'') as date) <= '2018-05-16') 
order by id desc

In both cases in the results you will also obtain the rows with logouttime is null

Upvotes: 3

Related Questions