Could any one of you please help me to write a query which will find the date from yesterday 12.00am till yesterday 11.59.59PM. that means yesterday 24 hrs only. If I use getdate it will show me the date which is right now which i dont want. everyday i need to search the data from yesterday whole day. Any help will be appreciated,
Something like this should work. I am subtracting one date from getdate and converting the date so the time will not matter.
WHEREConvert(varchar(10),Table1.DteColumn,101) = Convert(varchar(10),getdate() -1,101)
- Unmarked as answer by Naomi NModerator Wednesday, October 07, 2015 3:33 PM
I quite like this method: very clean (even if I do say so myself)Code Snippet
DECLARE @today datetime
SET @today = DateAdd(dd, DateDiff(dd, 0, GetDate()), 0)
WHERE mydate < @today
AND mydate >= DateAdd(dd, -1, @today)
I just wanted to thank you for a clear and precise snippet that solved a problem for me.
dbo.EventLog.OriginalEvent >=dateadd(d,datediff(d,0, getdate()),-1) and dbo.EventLog.OriginalEvent<dateadd(d,datediff(d,0, getdate()),0) and
dbo.EventLog.Tag LIKE '%LTCBMS\NC%' and
Just to adjust your where clause slightly:
dbo.EventLog.OriginalEvent >=dateadd(d,datediff(d,0, getdate()) -1, 0)
The reason for this is that the the second parameter of DateAdd is the number of units to add/remove from a date value, the 3rd parameter is the date itself.
If you ever use the smalldatetime data type, the query as you wrote it will fail, because -1 will cause an arithmetic overflow when converting to datetime, whereas zero will not!
To help illustrate what I mean:
DECLARE @datetime datetime , @smalldatetime smalldatetime BEGIN TRY SET @datetime = -1 SELECT @datetime END TRY BEGIN CATCH SELECT Error_Message() END CATCH BEGIN TRY SET @smalldatetime = -1 SELECT @smalldatetime END TRY BEGIN CATCH SELECT Error_Message() END CATCH
Assuming you may have an index on Date field, Datediff solution will not allow to use it since the query will be not sargable.
Premature optimization is the root of all evil in programming. (c) by Donald Knuth
Naomi Nosonovsky, Sr. Programmer-Analyst
I like this very much...
I'm not a advanced user of SQL nor a beginner.
I have a view for a table to show the record for the present day which is:
SELECT TOP (100) PERCENT UpdateTime, UserName, Application, ServerName, SessionState, LogonTime
WHERE (UpdateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
ORDER BY UpdateTime DESC
I would like to have the same thing but for all records from last 7 days.
What would you suggest???
Team is a group in which members work together to achieve a common goal.
SELECT UpdateTime, UserName, Application, ServerName, SessionState, LogonTime FROM dbo.CXA_UserSessions WHERE UpdateTime >= DATEADD(day, DATEDIFF(day, '19000108', CURRENT_TIMESTAMP), '19000101') ORDER BY UpdateTime DESC
BTW, ORDER BY is ignored in views (unless you specify TOP (N) where N is less than 100 percent).
For every expert, there is an equal and opposite expert. - Becker's Law
- Edited by Naomi NModerator Wednesday, October 26, 2011 3:46 PM