date time query confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

date time query confusion

Author

Topic

kshahzad
Starting Member

45 Posts

Posted - 2013-07-17 : 11:51:26

i have this working query below,

i want to filter the record based on current date.

In the query i have to hardcode the date vlaues but i want this to dynamically grab the current date.

the column type is datetime.

select avg(TotalUsers) from UsersLoggedIn where TimeRecorded between '2013-07-16 08:26:26.173' and '2013-07-17 17:00:30.277'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:54:54

[code]
select avg(TotalUsers) from UsersLoggedIn where TimeRecorded >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1) AND TimeRecorded < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kshahzad
Starting Member

45 Posts

Posted - 2013-07-17 : 13:28:32

what about this

TimeRecorded >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND TimeRecorded < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 13:34:49

quote:
Originally posted by kshahzad

what about this

TimeRecorded >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND TimeRecorded < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

this wont start from previous date. You had start date as 16th Jul which was yesterday
Thats why I started with -1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kshahzad
Starting Member

45 Posts

Posted - 2013-07-17 : 13:37:53

i want to collect only of current date at the end of the day like after 9 PM
so i guess this would fit will in this scenario Correct?

TimeRecorded >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND TimeRecorded < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 00:45:27

quote:
Originally posted by kshahzad

i want to collect only of current date at the end of the day like after 9 PM
so i guess this would fit will in this scenario Correct?

TimeRecorded >=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) AND TimeRecorded < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

Ok current day means above logic is correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources