none
sql server function to pull Fridays, Saturday and sunday data if the current day is monday

    Question

  • Hi

    The below query i hv used in where clause and it pull the data of fridays if the current day is monday, i need a code to pull the data of firday, saturday and suday if the current day is Monday, else one day previous data

    and

     

    convert(varchar,orderdate,101) = (case

    when

     

    datename(dw,getdate()) = 'Monday' then convert(varchar,dateadd(dd,-3,getdate()),101)

    Else

     

    convert(varchar,dateadd(dd,-1,getdate()),101) end)

    Monday, March 14, 2011 5:37 AM

Answers


  • You need to use dynamic sql. Try with:

    declare @sqlcmd nvarchar(1000)
    declare @a varchar(1000)
    select @a= case when

    datename(dw,getdate()) = 'Monday' then ''''+ convert(varchar,dateadd(dd,-1,getdate()),101) +''',' +

    ''''+convert(varchar,dateadd(dd,-2,getdate()),101) + ''',' + ''''+convert(varchar,dateadd(dd,-3,getdate()),101)+''''

    Else ''''+convert(varchar,dateadd(dd,-1,getdate()),101)+'''' end


    SET @sqlcmd = N'select   *  from YourTable
    where  convert(varchar,orderdate,101) in (' + @a +')'

    EXEC sp_executesql @stmt   = @sqlcmd

    In SET @sqlcmd you need to change your code.

    HTH
    Monday, March 14, 2011 1:43 PM

All replies

  • Hi,

    could you try with the below query.

    convert(varchar,getdate(),101) in case case when

    datename(dw,getdate()) = 'Monday' then ''''+ convert(varchar,dateadd(dd,-1,getdate()),101) +''',' +

    ''''+convert(varchar,dateadd(dd,-2,getdate()),101) + ''',' + ''''+convert(varchar,dateadd(dd,-3,getdate()),101)+''''

    Else ''''+convert(varchar,dateadd(dd,-1,getdate()),101)+'''' end

     


    Thanks, RajaSekhara Reddy . K
    Monday, March 14, 2011 6:09 AM
  • Thanks for the query but unfortunately query has executed with no data, i rechecked the table there are data available.

    Monday, March 14, 2011 6:47 AM
  • Hi Ranjit,

    did you replace Getdate() with orderdate in the above query?


    Thanks, RajaSekhara Reddy . K
    Monday, March 14, 2011 7:19 AM
  • Why can't you have a if clause to find if today is monday then do processing?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, March 14, 2011 7:23 AM
    Moderator
  • no i did not change anything.

    and

     

    convert(varchar,orderdate,101) in (case when

    datename

     

    (dw,getdate()) = 'Monday' then ''''+ convert(varchar,dateadd(dd,-1,getdate()),101) +''',' +

    ''''

     

    +convert(varchar,dateadd(dd,-2,getdate()),101) + ''',' + ''''+convert(varchar,dateadd(dd,-3,getdate()),101)+''''

    Else

     

    ''''+convert(varchar,dateadd(dd,-1,getdate()),101)+'''' end)

    Monday, March 14, 2011 8:35 AM
  • Thanks for you advise , as am very new to this can you please help me.

    My date column is called as Order Date

    Monday, March 14, 2011 8:41 AM
  • declare @d datetime
    set @d = getdate()


    declare @baseMonday datetime
    set @baseMonday = '17530101'

    select
      @d,
        @baseMonday + datediff(day,@baseMonday,@d)/7*7+4 as Friday,
      @baseMonday + datediff(day,@baseMonday,@d)/7*7+5 as Saturday,
      @baseMonday + datediff(day,@baseMonday,@d)/7*7+6 as Sunday


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, March 14, 2011 11:25 AM
    Answerer

  • You need to use dynamic sql. Try with:

    declare @sqlcmd nvarchar(1000)
    declare @a varchar(1000)
    select @a= case when

    datename(dw,getdate()) = 'Monday' then ''''+ convert(varchar,dateadd(dd,-1,getdate()),101) +''',' +

    ''''+convert(varchar,dateadd(dd,-2,getdate()),101) + ''',' + ''''+convert(varchar,dateadd(dd,-3,getdate()),101)+''''

    Else ''''+convert(varchar,dateadd(dd,-1,getdate()),101)+'''' end


    SET @sqlcmd = N'select   *  from YourTable
    where  convert(varchar,orderdate,101) in (' + @a +')'

    EXEC sp_executesql @stmt   = @sqlcmd

    In SET @sqlcmd you need to change your code.

    HTH
    Monday, March 14, 2011 1:43 PM
  • Hi Ranjith,

    Please try with the following query.

    if (datename(dw,getdate())  = 'Monday')

    Begin

    -- paste ur select code part here

    convert(varchar,orderdate,101) in (convert(varchar,Getdate()-3,101),convert(varchar,Getdate()-2,101),convert(varchar,Getdate()-1,101))

    End

    else

    Begin

    -- paste ur select code part here

    convert(varchar,orderdate,101) = convert(varchar,dateadd(dd,-1,getdate()),101)

    End

     


    Thanks, RajaSekhara Reddy . K
    Monday, March 14, 2011 2:12 PM