11/18/2016

SSRS date time expressions

Below are SSRS expressions which are used for date operations:

-       If we want to show date parameters on SSRS report for start date and end date and we want to show default value for start date as current month’s start date and for End date parameter it should be end date of current month, use below expressions:

Start Date Parameter, Default value expression

=DateSerial(Year(Now()), Month(Now()), “1”).AddMonths(-1)

End Date Parameter, Default value expression

=DateSerial(Year(Now()), Month(Now()), “1”).AddDays(-1)

-       First Day of Current Month:

=DateAdd("d",-(Day(today)-1), Today)

-        Last Day of Current Month:

=dateadd("m",1,dateserial(year(Today),month(Today),0))

-        First Day of Last Month:

=dateadd("m",-1,dateserial(year(Today),month(Today),1))

-        Last Day of Last Month:

=dateadd("m",0,dateserial(year(Today),month(Today),0))

-        First day of next month:

=dateadd(“m”,1,DateAdd(“d”,1-DatePart(“d”,Today()),Today()))

     -        First Day of Current Year:

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

-        Last Day of Current Year:

=DateSerial(Year(Now()), 12, 31)

-        First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

-        Last Day of Last Year:

            =dateadd(DateInterval.Year,-1,DateSerial(Year(Now()), 12, 31))

      -        Format Data value:

              =Format(Parameters!Date.Value,"dd-MM-yyyy") --- 21-02-2016

              =Format(Parameters!Date.Value,"dd/MM/yyyy") --- 21/02/2016

              =Format(Parameters!Date.Value,"MMM-dd-yyyy") --- Feb-21-2016

              =Format(Parameters!Date.Value,"MMM-dd-yy") --- Feb-21-16

=MonthName(Month(DateAdd("M",-1,Parameters!AsOfDate.Value)))& "  "& (Year(DateAdd("Y",-1,Parameters!AsOfDate.Value))) ---September 2010

-        Monday of current week

=DateAdd(“d”, 2 – DatePart(“w”,Today) , Today)

=Today.AddDays(1- Today.DayOfWeek)

-        Friday current week

=DateAdd(“d”, 6 – DatePart(“w”,Today) , Today)

=Today.AddDays(5- Today.DayOfWeek)

-        End of this week

=DateAdd(“d”, 8 – Weekday(Today), Today).ToString(“dd/MM/yyyy”)

-        Start of the week (2 weeks ago)

=DateAdd(“d”, -(Weekday(Today)+12) , Today).ToString(“dd/MM/yyyy”)

-        Return Current Month Name

=MonthName(Month(Today()))

For more SSRS datetime expression, go through on below page:
Date Time Functions of SSRS expressions

FB Fun4You: Funniest Prank Ever - must watch!!