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.
| Author |
Topic |
|
dougancil
Posting Yak Master
217 Posts |
 Posted - 2011-04-14 : 14:55:29
|
I have the following query:
SELECT mOnCallAdd.SchedName,mOnCallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd
FROM mdr.dbo.mOnCallAdd
where DATEadd(DAY, mOnCallAdd.StartOnCalldate, '12/31/1899')= '4/14/2011' and dateadd(hour, moncalladd.Startoncalltime, '00:00:00') >= '1:45:00'
and DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.Startoncalldate, '12/31/1899'))) <= '4/14/2011 23:59:00'
and moncalladd.schedname ='ctka'
ORDER BY OnCallDate
and what I'm attempting to do is to get results from both the current time through the end of the day and it works for the most part but I did get these two lines of data back when I ran my query:
CTKA Test Data - Riggs, Bobby 2011-04-14 07:00:00.000 2011-04-14 12:00:00.000
CTKA Test Data - Stevens 2011-04-14 07:00:00.000 2011-04-14 17:00:00.000
Can someone please assist.
Thanks
Doug
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-14 : 15:01:15
|
Aren't they correct? They both fall wiithin
2011-04-14 01:45:00 and 2011-04-14 23:59:00
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
dougancil
Posting Yak Master
217 Posts |
Posted - 2011-04-14 : 16:15:17
|
Jim,
Sorry I changed the 01:45 to 13:45 and still see the same results. I am trying to get values that fall between the current time and the end of the day and just display those. So in production my query will look like this:
CREATE procedure dbo.sp_currentoncall
-- Add the parameters for the stored procedure here
@currentdate nvarchar(25),
@currenttime nvarchar(25),
@endofday nvarchar(25),
@schedname nvarchar (100)
AS
BEGIN
SET NOCOUNT ON;
SELECT mOnCallAdd.SchedName,mOnCallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd
FROM mdr.dbo.mOnCallAdd
where DATEadd(DAY, mOnCallAdd.StartOnCalldate, '12/31/1899')= @currentdate and dateadd(hour, moncalladd.Startoncalltime, '00:00:00') >= @currenttime
and DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.Startoncalldate, '12/31/1899'))) >= @endofday
and moncalladd.schedname =@schedname
order by oncalldate asc
End
GO
|
|
|
|
sharona
Yak Posting Veteran
75 Posts |
Posted - 2011-04-18 : 12:07:59
|
not sure if this helps, but i use this in my stored procedures so it converts the datetime parameters to pick up from
12:00 am to 11:59 pm
SELECT @start_date = convert(datetime,convert(varchar(10), @StartDate, 101))
SELECT @end_date = dateadd(ss, -1, dateadd(dd, 1,convert(datetime,convert(varchar(10), @EndDate, 101))))
|
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-04-19 : 18:01:45
|
[code]
select <whatever>
from table t
where t.dateColumn >= getDate() and
t.dateColumn < DATEADD(d,DATEDIFF(d,0,GETDATE()),0)+1
[/code]
See:[url]http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx[/url]
Mirko
My blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
|
|
|
|
|
end of the day