0

i am looking for If current day’s date < 15th day of the month then Report start date = 1st day of (current Month-3) or else If current day’s date > 15th day of the month then Report start date = 1st day of (current Month-2).

Could anyone please help.

SATYAJIT MOHANTY
  • 11
  • 2
  • Possible duplicate of [How to get first and last day of previous month (with timestamp) in SQL Server](http://stackoverflow.com/questions/11743810/how-to-get-first-and-last-day-of-previous-month-with-timestamp-in-sql-server) – BIDeveloper May 13 '16 at 07:25
  • Have you actually tried anything before posting here? Show us what you have tried. – BIDeveloper May 13 '16 at 07:26
  • Hi, yes i did try but the nested iff and case doesnt work in SSRS parameter. – SATYAJIT MOHANTY May 13 '16 at 09:50

1 Answers1

0

Set Parameter value using below code.

  SELECT CASE WHEN (DATEPART(dd,getdate()) <15)
             THEN  CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-3, 0)) 
              ELSE  CONVERT(DATE,DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-2, 0)) end AS FirstDayOfPrevMonth
sandeep rawat
  • 4,797
  • 1
  • 18
  • 36
  • Thanks Sandeep, but i was actually looking for the SSRS date parameter input query, and the requirement is for example : if today's date that is 13th which is <15th then the startdate should be 1st of March and if today's date is suppose 16th of May which is >15th of the current month then the start date should be 1st of April. – SATYAJIT MOHANTY May 13 '16 at 09:49
  • I cannot make it hidden as this will be the default selected date and if i wish then i should also be able to change the date and view the report, initially i thought of this but the requirement is also to change the date as desired. – SATYAJIT MOHANTY May 13 '16 at 10:07
  • Create an hidden parm with this ,and create a parm which have dates and set default vale using the hidden parm. – sandeep rawat May 13 '16 at 10:21
  • Thanks, but i tried and this worked =IIf(DatePart("d",Today())<=15,DateAdd("m", -2, DateSerial(Year(Now()), Month(Now()), 1)),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))) – SATYAJIT MOHANTY May 13 '16 at 12:32