August 10, 2011 at 12:16 pm
>= @todaymidnight and < dateadd(d, 1, @todaymidnight)
August 10, 2011 at 12:20 pm
Ninja,
Is @todaymidnight the same as @sincedate? Since I've already declared that. Just to clarify.
August 10, 2011 at 12:27 pm
doug 40899 (8/10/2011)
Ninja,Is @todaymidnight the same as @sincedate? Since I've already declared that. Just to clarify.
You never posted that code in this thread, so assuming it's containing today's midnight datetime then yes. Using Gails demo : DATEADD(dd,0,DATEDIFF(dd,0,GetDate()))
August 10, 2011 at 1:10 pm
Ninja,
the variable of @sincedate is just declared as datetime. If I run this SP with just the date, then I have no issue, but as Gila stated, if I run my query with system time, then I'm never returned a result. I guess I need to know the correct way to run the where part of my query so that if I am looking for a specific time, I can match the timestamp of the server against my query to make sure that it falls within those parameters.
August 10, 2011 at 1:15 pm
Actually you need to see (and show us) the real values.
Run the select without the filters, run select @sincedate what you are filtering one, also select a new column => DATEADD(DAY, mOnCallAdd.AddDate, '12/31/1899') to see what value you are trying to filter on.
From that point on it's very easy to find the solution.
August 10, 2011 at 7:55 pm
Ninja's_RGR'us (8/10/2011)
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()))
This will also work...
SELECT DATEADD(mi,DATEDIFF(mi,0,GETDATE()),0)
--Jeff Moden
August 11, 2011 at 3:37 pm
Jeff,
Thank you for your response. What I've since discovered is as follows:
My query needs to return values for date/times up to the current time. In other words, say I have a schedule that starts at 12 pm today and it ends at 8 pm this evening and I run my query, I need to be able match that query based that it matches that its today AND between 12:01 am and 12:00 pm midnight. So I need my query to check between midnight and up to the current time, based on date and have all values that haven't ended before the current time be presented to me. Is that possible?
Thanks
Doug
August 11, 2011 at 3:43 pm
where >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND <= GETDATE()
for midnight next date, just put 1 instead of the 1 0 and then use < instead of <=
August 12, 2011 at 8:25 am
Ninja,
Here's how I have the SP coded now:
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(D, 0, DATEDIFF(D, 0, GETDATE())) AND <= 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(D, 0, DATEDIFF(D, 0, GETDATE())) AND <= GETDATE() and
mOnCallDelete.SchedName = 'arc im'
and I'm getting a syntax error in the where statement:
Line 10: Incorrect syntax near '>'.
Line 24: Incorrect syntax near '>'.
What do I have coded improperly?
Thank you
Doug
August 12, 2011 at 9:00 am
>= is a comparaison operator. You need to compare something to something else. Which will return true or false. Which is what is then applied as a filter.
August 12, 2011 at 12:17 pm
Ninja,
I'm aware that <>= are comparative operators. What I was asking is where does your code fit into what I'm asking to do? My code is complete and all I'm seeking is how to do what I need my query to do. What I'm not clear on is what I need to compare to ... I know that get date is the current date and time from the server. I can get my query to return all the results for any given day, but can't get my query to return results up to the current time and no further. Any help would be appreciated.
Doug
August 12, 2011 at 12:59 pm
See something missing between where and >??
where >= DATEADD
August 12, 2011 at 1:31 pm
I'm not sure what you're saying that >= is there. Are you saying that I'm not seeing something else?
August 12, 2011 at 1:38 pm
where a >= b
August 12, 2011 at 2:45 pm
Actually ... in going back through my code, I realized I missed something. The time and date for this process is stored in two fields. Start time and start date. Both of which are timestamp values. So I think that I'll have to do a datepart for both portions of the query in the Where clause.
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))
that query gives me a start time and date.
DATEADD(MINUTE, mOnCallDelete.duration,
DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')))
that gives me an end time. So my query will have to be against both those portions to make sure that it's not after the getdate. Any suggestions on how to do that?
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply