Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?


Addendum (was an answer, merging in -- jcolebrand)

Richard,

First let me thank you for your insight about the design of this table, and I will agree with you that it was poorly designed, but I didn't design it (thank god.) Secondly, I tried your query and I am getting better results, but it's still not eliminating all of the data that shouldn't be returned yet. Here's two results from my dataset that should have not shown up:

HEART HOSPITAL - xxxxx 2011-08-15 19:00:00.000 2011-08-16 07:00:00.000 HEART HOSPITAL - xxxxx 2011-08-15 13:00:00.000 2011-08-15 19:00:00.000 (the query was run at 9:18 CST so these wouldn't have occured yet.)

When I run this query:

SELECT DATEADD(DAY,38490,'12/31/1899'), CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) (38490 is the int in my first value of the StartOncallDate field)

it returns a value of: 2005-05-19 00:00:00.000 2011-08-15 00:00:00.000

Also here are a couple of lines of data in my table:

     Rec Id  Timestamp        SchedName     StartOnCallDate   StartOncallTime    FirstListing     Duration       AddDate  AddTime 
    70550    55426893      BITTAR&ESKEW        38490               1020             YIUM            840           38490    1293                                                             
    70551    55427287        ZZOB02            38494               1020         CARTER, KIMBERLY    900           38491     247 
share|improve this question
    
Which version of SQL Server are you using? I presume these are all INTEGER columns. Is that correct? Also, could you give us a couple of sample data rows for these tables? :) –  Richard Aug 12 '11 at 20:45

2 Answers 2

1) I'm not going to argue database design, since it seems that it's already in place.

2) Looking at the code, I have to presume that StartOnCallDate, StartOnCallTime, and Duration are Integers. If you're trying to do that with DATETIME, then there are other issues to deal with.

To answer your question...

First off, the math in your where clause is still including the time (hours and seconds). So this stuff:

DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))

Gives this result:

SELECT DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
        DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
-----------------------
2011-08-12 15:35:00.000

So, when you compare DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899') to that mess above, it will almost never be equal. That's because when you use DATEADD(DAY...) to that hard-coded date, you will only end up with values that are days:

SELECT DATEADD(DAY, 40800, '12/31/1899')
-----------------------
2011-09-15 00:00:00.000

To drop the hours and seconds, you could do a bunch more DATEADD stuff, or you could just do something like this:

(SQL Server 2008):
CONVERT (date, GETDATE())

(SQL Server 2005/2008 options):
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Those will get you JUST the date. If you throw one of those into your where clause, it will allow you to filter the data, showing you all values that match the current DAY (anything that is going to occur today):

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME) AND
    mOnCallAdd.SchedName = 'arc im' 

To give you an idea of the values you are comparing, pull each of those out (with sample data) into a select statement:

SELECT 
    DATEADD(DAY, 40766, '12/31/1899'), 
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
----------------------- -----------------------
2011-08-12 00:00:00.000 2011-08-12 00:00:00.000

Now, to filter out anything that might have already passed, you want to add a condition to your WHERE clause that checks the ending time to determine if that is earlier that the current time.

 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate()

This makes the entire where clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     mOnCallAdd.SchedName = 'arc im'

EDIT:

If you only want items to show up that are currently happening, you need to qualify on both your end time (as above) and your start time. To qualify your start time, you need to get your start time and make sure that the current time is greater than or equal to the current time:

DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
        DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate()

This makes the entire WHERE clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate() AND
     mOnCallAdd.SchedName = 'arc im'
share|improve this answer

Richard, here's the final result I got (which works)

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(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) 
BETWEEN Convert(VARCHAR(10), GetDate(), 101) 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(MINUTE, mOnCallDelete.StartOnCallTime,
DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) 
BETWEEN Convert(VARCHAR(10), GetDate(), 101) AND GetDate() and
 mOnCallDelete.SchedName = 'arc im'
share|improve this answer
    
Nice use of the BETWEEN keyword. What happens (and what do you want) if the item already ended, though? For example, if it's 8pm and it was scheduled from noon to 6pm. It would still show up in this list. You need to check both the "start" and "end" times in this case. Really, though, these little decisions are just business rules. It's the technique that's important. –  Richard Aug 15 '11 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.