Create View Set DateFirst

[jadams0173]

Using SQL 2000
I have a SP that I run when asked to provide some data. I've been asked to make this available real time. No problem, just plop the SP into a view and I'm done. Not so easy I'm finding out.

The view doesn't like the line in red:

create view V_ON_TIME_DELIVERY

as

[b][red]set datefirst 6[/red][/b]

select A.PicklistID, A.MO, A.SECTOR,
 RequestDate +
       Case DelTime
            When 'Noon'     Then '12:00'
            When 'Midnight' Then '0:00'
            Else DelTime
            End "Request_Delivery_DTS"
,A.REC_DATE,

DATEDIFF ( N, REC_DATE, RequestDate +
       Case DelTime
            When 'Noon'     Then '12:00'
            When 'Midnight' Then '0:00'
            Else DelTime
            End   ) "ON_TIME_MINUTES",

'ONTIME' =
case 
	When DATEDIFF ( N, REC_DATE, RequestDate +
       Case DelTime
            When 'Noon'     Then '12:00'
            When 'Midnight' Then '0:00'
            Else DelTime
            End   ) >= 0 then 'ON TIME'
	When DATEDIFF ( N, REC_DATE, RequestDate +
       Case DelTime
            When 'Noon'     Then '12:00'
            When 'Midnight' Then '0:00'
            Else DelTime
            End   )< 0  Then 'LATE'
	Else 'Not Delivered'
	end ,

convert(varchar,dateadd(dd, 7 - datepart(dw, A.REQUESTDATE), A.REQUESTDATE),101) "WEEK_ENDING"
from KITREQUESTLOG A
INNER JOIN WAITINGFORPICKLIST B
ON B.PICKLISTID = A.PICKLISTID
	AND B.SECTORID = A.SECTOR
	AND B.MO = A.MO

 

[gmmastros]

Try using the datefirst command when you call the view (not when you create the view). What I mean is.... you MUST take the datefirst setting out of the view in order to create it.

Then, when you query the view....

[tt][blue]
Set DateFirst 6
Select * From V_ON_TIME_DELIVERY Where blah....
[/blue][/tt]

I honestly haven't had time to test this, but I think it'll do what you need.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom