Getting current date as part of an Stored Procedure

[dougancil]

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.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , 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.StartOnCallDate, '12/31/1899')= DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE())) and
    mOnCallDelete.SchedName = 'arc im'
 

and what I'm trying to do is as follows:

If I have a schedule that runs from 9 a.m. today to 5 p.m. today, and I run my SP anytime during that time frame, I'd like to see that schedule as part of my dataset. I can make this work for date alone, but can't figure out the time portion. Can anyone offer any assistance on this?

Thank you

Doug

[Rabbit]

I'm not sure what you want because you're already using getdate which will return the current date and time.

[dougancil]

Rabbit,

It's not producing any results. For example, if I run this query by date alone, then it'll work . But because my schedules have start times and end times and what I'm looking to do is something with a BETWEEN statement, but I just don't know what. It would be something like between midnight and getdate ... I just don't know how to write it.

For example ... when I run this with date alone ... here's what I get:

SETON NW - 2011-08-12 19:00:00.000 2011-08-13 07:00:00.000
HEART HOSPITAL - 2011-08-12 07:00:00.000 2011-08-12 13:00:00.000
SETON MAIN - 2011-08-12 07:00:00.000 2011-08-12 17:00:00.000
SETON MAIN - 2011-08-12 17:00:00.000 2011-08-12 23:00:00.000


and you can see that it shows me both values before the current time and after the current time. What I need to be able to do is to see up to the current time but no later.

[Rabbit]

DateVar BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate()

[dougancil]

I'm not sure what you mean as the DateVar. Are you saying that I need to leave in

   WHERE DATEADD(DAY, mOnCalldelete.AddDate, '12/31/1899')
 

because when I leave that in and run the query, it gives me too many results.

SETON MAIN ADMITS - 2011-08-17 23:00:00.000 2011-08-18 07:00:00.000
SETON MAIN ADMITS - 2011-08-18 07:00:00.000 2011-08-18 15:00:00.000
SETON MAIN ADMITS - 2011-08-19 15:00:00.000 2011-08-19 23:00:00.000
SETON MAIN ADMITS - 2011-08-13 19:00:00.000 2011-08-14 07:00:00.000
SETON MAIN ADMITS - 2011-08-16 15:00:00.000 2011-08-16 23:00:00.000
SETON MAIN ADMITS - 2011-08-17 15:00:00.000 2011-08-17 23:00:00.000
SETON NW - 2011-08-12 07:00:00.000 2011-08-12 19:00:00.000

[Rabbit]

DateVar is the name of whatever date variable/field you're trying to filter on. Basically, what I posted will give you all records where DateVar is between '08/12/2011 00:00:00.000' and '08/12/2011 13:21:52.158' or whatever time it is currently.

[dougancil]

Rabbit,

As you can see from my query, the only date/variable that I have in this is where I have this line:
where DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')

because i have to convert the table that I'm pulling from to a readable date, there is another field for time. So I have start time and start date as two fields. So I guess, I'll need to have one query for the date, which I guess I'll have to do a datepart of the getdate to provide me with just a date and then another query for just the time.

So then my query will need to look something more like this:

WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899') BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and

and then the datepart will have to be done for both parts. Any suggestions on how to do that?

[Rabbit]

There's no need to separate them. And there's no need for DatePart(). I don't know why they are separated in the first place, because they are readable as a regular datetime data type. All you need to do is change DateVar in my example to the datetime value you need. In this case, combining your date field and your time field and converting it to a datetime.

[dougancil]

They are seperated in the database. It wasnt by choice that it was done this way. The date is always going to be the current date (whatever that is) and the current time and will need to match both the start date, and be before or up to the current date/time. But as the values in the table are split as one field for date, and one for time, thats' why I assumed that it would have to be split to find those values. I won't be using a variable for the current date, I've got to find a way to get that as one value and the time as one value and match them both.

(from the table in the database)
This is the value for startdate
38490
this is the value for starttime
1020

[Rabbit]

DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,
   DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) 
   BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate()

[dougancil]

Rabbit, that was it. Thank you very much.