Returning values in a SP for current date/time

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)

Returning values in a SP for current date/time

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-10 : 12:41:06

I have the following SP:

SELECT
moncallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
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.AddDate, '12/31/1899') = @currentdate and
mOnCallAdd.SchedName = 'ARC IM'

UNION
SELECT
moncallDelete.FirstListing,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,
DATEADD(MINUTE, mOnCallDelete.duration,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd
FROM
mdr.dbo.mOnCallDelete
WHERE
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') = @currentdate AND
mOnCallDelete.SchedName = 'ARC IM'

and for the variable @currentdate, I'd like to have this query pull only the information for the current date and time based on the server date/time. Can anyone offer me a way to do that?

Thank you

Doug

Mar
Starting Member

47 Posts

Posted - 2011-08-11 : 13:33:10

Instead of:
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') = @currentdate AND

Use this:
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') = convert(varchar,getdate(),101) AND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-11 : 13:51:03

better to use

DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899') < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-11 : 13:58:19

Couldn't you also do
mOnCallDelete.AddDate = DATEDIFF(dd,'18991231',GETDATE())
?

jim

Everyday I learn something that somebody else already knew

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-11 : 16:29:06

Mar, Visakh and Jim, Thank you. It solves half of my problem. What I'm trying to do is as follows:

I have an schedule that starts today at 7 a.m. and runs through 5 p.m. tonight. What I need my query to return is any result that matches the current date up to the current time. So if I ran my query at 3 pm. today, this schedule that starts at 7 a.m. would be returned. I need the query to return values back for the date and the time, based on it's finding something that occured between 12:01 a.m. today and the current time. Does that make sense?

dougancil
Posting Yak Master

217 Posts

Posted - 2011-08-11 : 16:51:51

Oh and I should also state that I'll never have static values for either date or time, it'll always be the server time that I'm using to run my query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources