March 13, 2011 at 4:03 am
Sir/Madam, i need some suggestion in retrieving data between the current time and the ending time of a day. i could not able to retrieve data. Ending time in the sense that i must need to retrieve the data upto the end of a day, suppose my day time begins with 00:00:00, then i must retrieve the data upto 23:00:00.
For example- if my current time is 11:00:00 then i must retrieve the data starting from the current time i.e 11:00:00 to the ending time i.e 23:00:00. similarly if the current time is 13:00:00 then i must retrieve data from 13:00:00 upto 23:00:00. plz help me in this regard so that i can continue further.
Thank u in advanced. Any help from u will highly be appreciated.
March 13, 2011 at 4:58 am
generally we solve this kind of problem using e.g.
select ...
from ...
where yourdatecol >= GETDATE()
and yourdatecol < DATEADD(dd, datediff(dd, 0,getdate()) + 1,0)
Don't use "between" because that may cause problems with the datetime datatype columns.
(I don't have a kb ref for that)
btw Have a look at :
"Some Common Date Routines"
http://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
By Lynn Pettis
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2011 at 9:35 am
Thank u Sir. Ur query give me the result. But i need something little different result. When starting time is suppose 10:20:53, ur query gives me the result from 11:00:00 to ending time i.e 23:00:00, i need to be started from 10:00:00 i.e if starting time is in between 10:00:00 to 10:59:59 i must have the same data whose starting time as 10:00:00 upto 23:00:00.
i have a database table maintaining time as an interval of 1hour i.e like 09:00:00, 10:00:00, 11:00:00... upto 23:00:00.
So, i must have 10:00:00 to 10:59:59 the same data which is maintained at time 10:00:00. Similarly, when it will be 11:00:00 i must have the data of 11:00:00 same upto 11:59:59...
plz, sir only slight change is need to made in the query but i am trying still not in the right track. i cannot able to find the result.
Thank u once again. Sir, i would be grateful to u if u again give me some suggestion to overcome out of this problem...
March 13, 2011 at 10:00 am
This plus the info from ALZDBA should provide the tools to reach your solution.
DECLARE @ThisDate DATETIME = GETDATE() ;
SELECT
DATEADD(hh, DATEDIFF(hh, 0, @ThisDate), 0)
AS [beginning of the current hour] ,
DATEADD(hour, -1, DATEADD(dd, DATEDIFF(dd, 0, @ThisDate) + 1, 0))
AS [23:00:00 of the current day]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2011 at 12:10 pm
Thank you very much. Thank you Sir. now i can proceed further...........
Your query is an immense help for me....... Thank you once again...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply