end of the day - 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

SQL Server 2000 Forums

Transact-SQL (2000)

end of the day

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources