SQL Query errors doesn't return values for Fridays

Question

I have this sql stored procedure which is supposed to look at a particular table and return job numbers based on a date column. This works great except for when its run Saturday morning (and should return all job numbers with a date of Friday, but returns no rows). Any suggestions? Is there some logic problem here I'm not seeing? How might I track this down?

Stored Procedure

ALTER Procedure [dbo].[JC_GetJobsClosedYesterday]

As

SELECT [JobNumber]
  FROM [NCLGS].[dbo].[JobClosedDate]
  Where LastInvoiceDate between dbo.ufn_StartOfDay (DATEADD(d, -1, GETDATE())) AND dbo.ufn_StartOfDay (GETDATE())
  order by JobNumber desc

And the start of day function.

ALTER function [dbo].[ufn_StartOfDay] ( @inDate datetime )

RETURNS DateTime AS 
BEGIN   
DECLARE @Now datetime   
set @Now = @inDate
DECLARE @DayStart datetime
set @DayStart = @Now

set @DayStart = DATEADD (ms, -DATEPART(ms,@Now),@DayStart)
set @DayStart = DATEADD (s, -DATEPART(s,@Now),@DayStart)
set @DayStart = DATEADD (mi, -DATEPART(mi,@Now),@DayStart)
set @DayStart = DATEADD (hh, -DATEPART(hh,@Now),@DayStart)

return @DayStart 
END

EDIT: I'm not having trouble with my date conversion (unless it doesn't know how to handle Fridays). I need help with the returning no rows part.

SAMPLE DATA:

JobNumber   LastInvoiceDate             DayOfWeek
112117      2011-06-13 00:00:00.000     Monday
112089      2011-06-10 00:00:00.000     Friday
112090      2011-06-10 00:00:00.000     Friday
112068      2011-06-10 00:00:00.000     Friday
112082      2011-06-10 00:00:00.000     Friday

UPDATE: Now I'm really confused. This "no data on fridays" thing has been happening (happened again last friday), but I still can't figure it out. Is it possible that GETDATE() isn't returning what I think its returning? Because when I try the following modifications based on @Thomas's suggestion, both methods get data, but the report that generated based on this code last sat has no data.

DECLARE @date datetime
--SET @date = '2011-06-21 13:42:27.257'
SET @date = '2011-06-11 03:42:27.257'

--Original Code
SELECT [JobNumber]
  FROM [NCLGS].[dbo].[JobClosedDate]
  Where LastInvoiceDate between dbo.ufn_StartOfDay (DATEADD(d, -1, @date)) AND dbo.ufn_StartOfDay (@date)
  order by JobNumber desc

  --Returns 21 records

--Modified based on @Thomas suggestion
Select [JobNumber]
From [NCLGS].[dbo].[JobClosedDate]
Where LastInvoiceDate >= DateAdd( d, DateDiff( d, 0, @date ) - 1, 0 )
    And LastInvoiceDate < DateAdd( d, DateDiff( d, 0, @date ), 0 )
Order By JobNumber Desc

  --Returns 21 records

Answer 1

Instead of DATEADD(d, -1, GETDATE()) you should use an expression that returns the beginning of the previous day. You could use your dbo.ufn_StartOfDay() function for that, but there's simpler way to do the same:

Select @DayStart = DateAdd( d, DateDiff( d, 0, @inDate ), 0 )

which means: increase the nil timestamp by the whole number of days between the nil timestamp and the given one.

I would also suggest using that expression instead of the already present call to the function as well, so your query would be:

Select [JobNumber]
From [NCLGS].[dbo].[JobClosedDate]
Where LastInvoiceDate Between DateAdd( d, DateDiff( d, 0, GetDate() ) - 1, 0 )
                          And DateAdd( d, DateDiff( d, 0, GetDate() ),     0 )
Order By JobNumber Desc

---

Addition

You need to be clearer about where exactly the problem is. Here is a sample query I created which tests every date from Thursday, June 9 through Saturday June 18. On which date did you expect to get values but did not or visa versa:

With SampleData As
    (
    Select 112117 As JobNumber, '2011-06-13 00:00:00.000' As LastInvoiceDate, 'Monday' As DayOfWeek
    Union All Select 112089, '2011-06-10 00:00:00.000', 'Friday'
    Union All Select 112090, '2011-06-10 00:00:00.000', 'Friday'
    Union All Select 112068, '2011-06-10 00:00:00.000', 'Friday'
    Union All Select 112082, '2011-06-10 00:00:00.000', 'Friday'
    )
    , TestDates As
    (
    Select Cast('20110609' As datetime) As Date
    Union All
    Select DateAdd(d,1,Date)
    From TestDates
    Where Date <= '20110617'
    )
Select TD.Date, DateName(dw,TD.Date), Count(SD.JobNumber)
From TestDates As TD
    Left Join SampleData As SD
        On SD.LastInvoiceDate Between DateAdd( d, DateDiff( d, 0, TD.Date ) - 1, 0 )
                          And DateAdd( d, DateDiff( d, 0, TD.Date ),     0 )

Group By TD.Date

---

Update

In looking at your comments and the code, I think the problem is in your use of Between. Col Between DateA And DateB translates to Col >= DateA And Col <= DateB. I.e., it is inclusive of both end points. Instead, you need to exclude the final end point:

Select [JobNumber]
From [NCLGS].[dbo].[JobClosedDate]
Where LastInvoiceDate >= DateAdd( d, DateDiff( d, 0, GetDate() ) - 1, 0 )
    And LastInvoiceDate < DateAdd( d, DateDiff( d, 0, GetDate() ), 0 )
Order By JobNumber Desc

This will give you all job numbers that on the previous date. I.e, if today is Friday, June 10, 2011, it will give you all LastInvoiceDate values from 2011-06-09 midnight through 2011-06-09 23:59:59.

Answer 2

See Floor a date in SQL server

To strip the time portion use:

 SELECT CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS DATETIME)

This should be the fastest way. Thus

SELECT [JobNumber]
  FROM [NCLGS].[dbo].[JobClosedDate]
  WHERE LastInvoiceDate between 
       CAST((FLOOR(CAST(GETDATE() float))-1.0) AS DATETIME) AND
        CAST(FLOOR(CAST(GETDATE() AS float)) AS DATETIME)
  ORDER BY JobNumber DESC

Answer 3

The procedure which updates the jobclosed table runs Monday, Tuesday, Wednesday, Thursday, & Friday mornings. It doesn't run on Saturdays, so the records aren't inserted until monday, at which time they won't be retrieved by my stored procedure. I will schedule the update job to run on Saturday's also.

I added the following before the select statement:

if datepart(dw, GETDATE()) = 7 OR datepart(dw, GETDATE()) = 1
    BEGIN
        Exec dbo.NCL_MaintainJobClosedDateTable
        --Select 'True'
    END

Which will force an update on Saturday and Sunday mornings.