August 10, 2011 at 10:32 am
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
August 10, 2011 at 10:46 am
Getdate()
Just be aware that's date and time to a 3 ms accuracy. If you're using = with it, the other value must match exactly (to 3 ms)
Generally inequalities are used with dates, not hard equality matches
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 11:01 am
Gila,
So then what would be a better approximation than that? Basically, what I'm looking to do is if it matches that date, then it'll display it.
August 10, 2011 at 11:16 am
In your case, because the date you're computing is at midnight, you can just calculate current date at midnight and use an =.
DECLARE @CurrentDate DATETIME;
SET @CurrentDate = DATEADD(dd,0,DATEDIFF(dd,0,GetDate()));
By the way, your query may give you performance problems later due to the dateadds. Any index on the AddDate columns can't be used for seeks because of the function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 11:23 am
Another PS. union = SELECT * FROM <2 queries> GROUP BY * (or select distinct).
This can be a very costly operation. If this is not the behavior you need you can use UNION ALL which does't do the distinct step.
August 10, 2011 at 11:27 am
Sorry about that, I've edited my SP and it now looks like this:
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') = DATEADD(dd,0,DATEDIFF(dd,0,GetDate())) 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') = DATEADD(dd,0,DATEDIFF(dd,0,GetDate())) AND
mOnCallDelete.SchedName = 'arc im'
that should work correct?
August 10, 2011 at 11:34 am
doug 40899 (8/10/2011)
Gila,Ok I'm not sure where to put that in with the SP. I assume because it's a declaration, that it goes before my SELECT statement. Correct?
Yes.
--making sure you don't screw up anything
BEGIN TRAN
use tempdb
GO
--your code would start here
CREATE PROCEDURE dbo.usp_demo
AS
DECLARE @dt DATETIME
SET @dt = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
SELECT * FROM sys.dm_exec_sessions WHERE login_time >= @dt
GO
--call the sp
EXEC dbo.usp_demo
--undo all changes
ROLLBACK
August 10, 2011 at 11:37 am
Ok so I was told just a moment ago that both hour and minute would need to be included in my SP. What's the syntax for that?
August 10, 2011 at 11:39 am
Yup. Anywhere after the beginning of the procedure and before you reference the variable in the select statements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 11:41 am
doug 40899 (8/10/2011)
Ok so I was told just a moment ago that both hour and minute would need to be included in my SP. What's the syntax for that?
SELECT
--removes ms, then s by substracting then from GETDATE()
DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
August 10, 2011 at 11:43 am
Thank you Gila and Ninja. I'll let you know if I have any more questions.
August 10, 2011 at 11:43 am
doug 40899 (8/10/2011)
Ok so I was told just a moment ago that both hour and minute would need to be included in my SP. What's the syntax for that?
With the way you're calculating a date from DATEADD(DAY, mOnCallDelete.AddDate, '12/31/1899'), that will never match anything other than midnight. So unless you change the way you're calculating a column from AddDate and whatever other columns, you do not want to add in hour and minute as it will never match any rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 12:01 pm
Gila,
Ok so then how would be a better way to calculate that?
August 10, 2011 at 12:06 pm
Err, dunno, what are you trying to calculate?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 10, 2011 at 12:13 pm
This is how my SP originally looked:
SELECT DATEADD(dd,0,DATEDIFF(dd,0,GetDate()))
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') = @sincedate AND
mOnCallAdd.SchedName = @schedname
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') = @sincedate AND
mOnCallDelete.SchedName = @schedname
and when I would use any date for the variable of @sincedate (the one I was testing with was '8/2/2011') and I was getting results back for that date. The only other thing that I wanted to modify would be the ability to have the SP always have the value for todays date as the understood date of the @sincedate and then for me to be able to just supply the @schedname. Are you saying that with the current version (because I'm restricting it with DATEADD(dd,0,DATEDIFF(dd,0,GetDate())) that my query will only find entries that show midnight? What I'm needing to do is to find any entry on the current date. Does that make more sense?
Thanks
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply