Questions

What is the proper syntax for using an if statement in a t-sql where clause

Tags:
+
0 Votes
Locked

What is the proper syntax for using an if statement in a t-sql where clause

rhardy
I want to filter my results based on the day of week of today's date. If today is Friday then I want to get results of everything with a date of Sat, Sun & Mon. But if it isn't Friday then I only want results from tomorrow.
  • +
    0 Votes

    How can you have "Results from tomorrow" when tomorrow is in the future?
    Unless you have a time machine we do not know of that puts you in the future and then brings you back, you will have no chance of doing your problem. Mind you if you had, then you will be dealing with a different kind of maths based on the Quantum leap theory. Unless of course you mean "from yesterday".
    :)

    +
    0 Votes
    oldbaritone

    why didn't you wait until tomorrow to ask?

    ;-)

    +
    0 Votes
    rhardy

    Thanks for the great reply, it brought a smile to my face! :)
    Actually I work in the newspaper industry and I really do want to find out what is scheduled to be published tomorrow.

    +
    0 Votes
    jgarcia102066

    DECLARE @Date AS DATETIME
    SET @Date = '6/4/2010'

    IF DATEPART(DW, @Date) = 6
    BEGIN
    SELECT 'Friday'
    END
    ELSE
    BEGIN
    SELECT 'Not Friday'
    END

    +
    0 Votes
    Tony Hopkinson

    Where ((DatePart(dw,getDate() >= 5) and ([Somedate] between dateAdd(d,getdate(),1) and DateAdd(d,getDate(),3)))
    OR
    ((DatePart(dw,getDate() < 5) and ([Somedate] = dateAdd(d,getdate(),1)))

    If that's too messy then

    Declare @StartDate DateTime
    Declare @EndDate DateTime
    set @StartDate = dateAdd(d,GetDate(),1)
    if DatePart(dw,getDate()) >= 5
    begin
    set @EndDate = dateadd(d,@StartDate,2)
    end
    Else
    Begin
    set @Endate = @StartDate
    end

    Select * From SomeTable Where SomeDate Between @StartDate and @EndDate

    Check what DateFirst is on your setup whether day one of the week is Sunday or Monday might mean you need to adjust the logic a bit.

  • +
    0 Votes

    How can you have "Results from tomorrow" when tomorrow is in the future?
    Unless you have a time machine we do not know of that puts you in the future and then brings you back, you will have no chance of doing your problem. Mind you if you had, then you will be dealing with a different kind of maths based on the Quantum leap theory. Unless of course you mean "from yesterday".
    :)

    +
    0 Votes
    oldbaritone

    why didn't you wait until tomorrow to ask?

    ;-)

    +
    0 Votes
    rhardy

    Thanks for the great reply, it brought a smile to my face! :)
    Actually I work in the newspaper industry and I really do want to find out what is scheduled to be published tomorrow.

    +
    0 Votes
    jgarcia102066

    DECLARE @Date AS DATETIME
    SET @Date = '6/4/2010'

    IF DATEPART(DW, @Date) = 6
    BEGIN
    SELECT 'Friday'
    END
    ELSE
    BEGIN
    SELECT 'Not Friday'
    END

    +
    0 Votes
    Tony Hopkinson

    Where ((DatePart(dw,getDate() >= 5) and ([Somedate] between dateAdd(d,getdate(),1) and DateAdd(d,getDate(),3)))
    OR
    ((DatePart(dw,getDate() < 5) and ([Somedate] = dateAdd(d,getdate(),1)))

    If that's too messy then

    Declare @StartDate DateTime
    Declare @EndDate DateTime
    set @StartDate = dateAdd(d,GetDate(),1)
    if DatePart(dw,getDate()) >= 5
    begin
    set @EndDate = dateadd(d,@StartDate,2)
    end
    Else
    Begin
    set @Endate = @StartDate
    end

    Select * From SomeTable Where SomeDate Between @StartDate and @EndDate

    Check what DateFirst is on your setup whether day one of the week is Sunday or Monday might mean you need to adjust the logic a bit.