0

I want to calculate the currently logged in users for which i tried the below query: 

select COUNT(*)from [dbname] 
where  login_time < SYSDATETIME()   
and logout_time is NULL

But i want to get the records for only current date so i tried :

select COUNT(*)from [dbname] 
where  ( SYSDATETIME()-1) < login_time < SYSDATETIME() 
and logout_time is NULL

This seems to be incorrect. So how to form the query properly? I cant use GETDATE() or Datetime.now() as it will give the secs as well which wont match with the datetime in the database

Peter Smith
  • 5,528
  • 8
  • 51
  • 77
Anita Mathew
  • 183
  • 1
  • 15
  • As for the later part of the query.. I have a mechanism to clear the logout time and set it as NULL when the user logs out and save it in a a lastlogin column. – Anita Mathew May 05 '18 at 08:57
  • Why not search for `logout_time` is NULL? If this field is not reliable then you might want to rethink your approach. If this is a web based application then getting logout time can be tricky. – Peter Smith May 05 '18 at 09:01
  • what do you expect sysdatetime -1 to do – Tomm May 05 '18 at 09:05

1 Answers1

0
SELECT COUNT(*) from [dbname] 
WHERE login_time < SYSDATETIME()  
AND CONVERT(date, login_time) = CONVERT(date, getdate())
AND logout_time is NULL

You want to use CONVERT(date, getdate()) as of SQL Server 2008

CONVERT(date, getdate()) Examples

Tomm
  • 1,021
  • 2
  • 14
  • 34
  • I don't think that `login_time < SYSDATETIME() ` is necessary? – Peter Smith May 05 '18 at 09:13
  • @PeterSmith its her way of checking if someone is logged in.. login_time lower then currenttime = logged in | and login_time = current date = today. This covers all of his needs – Tomm May 05 '18 at 09:15
  • I think it's 'her' :-). Also, how can the login time be in the future? – Peter Smith May 05 '18 at 09:17
  • Thats ryt..because I sometimes have records where the login time is for an older date and the logout time is null, so even those records are being counted. – Anita Mathew May 05 '18 at 09:17
  • @PeterSmith my apologies, and login_time can't be in the future. But not checking it equals that everything that has the same date as today is logged in. – Tomm May 05 '18 at 09:18
  • @Tomm Beg to differ. The last two conditions say logged in today and not logged out. The SYSDATETIME() adds nothing to that. – Peter Smith May 08 '18 at 11:06