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)

Answer 1

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

Answer 2

Thanks for the query but unfortunately query has executed with no data, i rechecked the table there are data available.

Answer 3

Hi Ranjit,

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

---

Thanks, RajaSekhara Reddy . K

Answer 4

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

Answer 5

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)

Answer 6

Thanks for you advise , as am very new to this can you please help me.

My date column is called as Order Date

Answer 7

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/

Answer 8

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

Answer 9

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