Create a view to force Friday date

  • Hello to all. I wonder if I any of you can help me with a simple question. I created a view to read data as per each Friday. I first hard coded the date for testing purposes, but now I am ready to use it in my life ssytem and don't want to have a hard coded date in my view.

    If I run the statement in query analyzer it runs fine, but when I tried to alter the view to accept datefirst I got an error message that reads:

    Server: Msg 156, Level 15, State 1, Procedure CurrentAmounts, Line 7

    Incorrect syntax near the keyword 'SET'.

    The statement is as simple as this:

    ALETRE VIEW MyView

    AS

    SET DATEFIRST 1

    DECLARE @Date DATETIME

    SET     @Date = GETDATE()

    SELECT DISTINCT TOP 100 PERCENT EmpID,

    CodeID, TaxDescription, BranchCode,

    CurrentDeductions, CurrentWages, CurrentGrossWages,

    CurrentSubjectWages, PayDate

    FROM table1 INNER JOIN table2 ON table1.CodeID = table2.CodeID

    WHERE (table1.MyDate = CONVERT(VARCHAR(8), DATEADD(DAY, 5 - DATEPART(WEEKDAY, @Date), @Date), 112))

     

    What am I doing wrong!!!! please help. Do I have to turn on or off any setting for the view to accept datefirst? or is just a matter of "user" error on syntax? Thank yoiu

  • A lot of things.

    1st of all, don't use SELECT TOP 100 PERCENT in views.

    If you need it for ORDER BY don't do it. It's really bad for performance.

    Perform all orderings in SP.

    2nd, view is single SELECT statements.

    No DECLARE or SET allowed.

    Your view may return additional column with last Friday before table1.MyDate for any particular line. And you filter by this value when you select from the view.

    If you want to have @@Datefirst independent results use this:

    (@@Datefirst + DATEPART(WEEKDAY, @AnyDate)  -2 )%7 + 1

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=235406

    _____________
    Code for TallyGenerator

  • Thanks, I will try that. Sorry for the top 100, I took the main statement and copy what I needed.  The order by goes in a different place and not within the view.

    The datefirst I am using is basically a paramenter to read my data with friday's date. I used date first in another step of the whole process to and return the right date.

    I will try to use your @@datefirst as a paramenter to see if that works better. Thank you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply