March 23, 2015 at 4:36 am
Hi,
So I ended up using the following expressions in my report in order to default dates in SSRS
Work out first monday of the previous month.
=IIF(Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))=2,
DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",9-Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))))
The day BEFORE the FIRST MONDAY of the CURRENT month:
Work out the sunday before the first monday of the current month.
=IIF(Weekday(DateSerial(Year(Now()), Month(Now()), 1))=2,
DateAdd("d",-1,DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",8-Weekday(DateSerial(Year(Now()), Month(Now()), 1)),DateSerial(Year(Now()),
For some reason when I'm running the report today it's defaulting to 09th of Feb and 8th of March. These should be 2nd of Feb (first monday of last month) and 1st of March (sunday before the first monday of current month)
Any idea why that would be?
March 24, 2015 at 9:50 am
You need to let SSRS figure out what weekday the first day of the month is. Your formulas have 8 and 9 hard coded into them.
To get the first Monday of the previous month, I created an expression that finds the weekday of the first day of the previous month and gets the date by adding days to it.
First Monday of the Previous Month:
=IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=1, DATEADD("d",1,CDATE(MONTH(TODAY())-1 & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=3, DATEADD("d",6,CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=4, DATEADD("d",5,CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=5, DATEADD("d",4,CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=6, DATEADD("d",3,CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))=7, DATEADD("d",2,CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))),
CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY()))))))))
Sunday before first Monday of Current Month:
=DATEADD("d",-1,IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=1, DATEADD("d",1,CDATE(MONTH(TODAY()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=3, DATEADD("d",6,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=4, DATEADD("d",5,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=5, DATEADD("d",4,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=6, DATEADD("d",3,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=7, DATEADD("d",2,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
CDATE(MONTH(Today())-1 & "/1/" & YEAR(TODAY())))))))))
March 25, 2015 at 12:48 pm
TSQL Tryer (3/23/2015)
Hi,So I ended up using the following expressions in my report in order to default dates in SSRS
Work out first monday of the previous month.
=IIF(Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)))=2,
DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",9-Weekday(DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))),DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))))
The day BEFORE the FIRST MONDAY of the CURRENT month:
Work out the sunday before the first monday of the current month.
=IIF(Weekday(DateSerial(Year(Now()), Month(Now()), 1))=2,
DateAdd("d",-1,DateSerial(Year(Now()), Month(Now()), 1)),
DateAdd("d",8-Weekday(DateSerial(Year(Now()), Month(Now()), 1)),DateSerial(Year(Now()),
For some reason when I'm running the report today it's defaulting to 09th of Feb and 8th of March. These should be 2nd of Feb (first monday of last month) and 1st of March (sunday before the first monday of current month)
Any idea why that would be?
I answered this yesterday, but for some odd reason, my post is gone....that is ok though. After thinking about it, I realized it needed a tweak.
To find the date of the first Monday of the month:
=IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=1, DATEADD("d",1,DATEADD("M",-1,CDATE(MONTH(TODAY()) & "/1/" & YEAR(TODAY())))),
IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=3, DATEADD("d",6,DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))),
IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=4, DATEADD("d",5,DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))),
IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=5, DATEADD("d",4,DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))),
IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=6, DATEADD("d",3,DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))),
IIF(Weekday(DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))))=7, DATEADD("d",2,DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))),
DateADD("m",-1,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))))))))
To get the Sunday before the first Monday of the current month:
=DATEADD("d",-1,IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=1, DATEADD("d",1,CDATE(MONTH(TODAY()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=3, DATEADD("d",6,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=4, DATEADD("d",5,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=5, DATEADD("d",4,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=6, DATEADD("d",3,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
IIF(Weekday(CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))=7, DATEADD("d",2,CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY()))),
CDATE(MONTH(Today()) & "/1/" & YEAR(TODAY())))))))))
March 25, 2015 at 5:48 pm
Try these if you like...
First Monday of previous month:
= DateAdd(DateInterval.Day, (8 - DatePart(Dateinterval.Weekday,DateAdd(DateInterval.Month, - 1, DateValue(Format(today, "MMM yyyy"))),firstdayofweek.Monday)) Mod 7, DateAdd(DateInterval.Month, - 1, DateValue(Format(today, "MMM yyyy"))))
And Sunday before first Monday of current month:
= DateAdd(DateInterval.Day, (8 - DatePart(Dateinterval.Weekday,DateValue(Format(today, "MMM yyyy")),firstdayofweek.Monday)) Mod 7 -1, DateValue(Format(today, "MMM yyyy")))
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 30, 2015 at 7:44 am
Thanks guys - as always appreciate the help.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply