SSRS Date Time Expressions
Date Time Functions of SSRS:
Below are Data time functions which are used in SSRS expressions:
To get current date and time according to system:
Time:
=Format(CDate(Globals!ExecutionTime), "MM-dd-yyyy hh:mm.ss")
Date:
=FORMAT(Cdate(today), "dd-MM-yyyy")
Today() - Returns or sets a Date value containing the current date according to your system:
=Today()
="Tomorrow is " & DateAdd("d",1,Today())
="Tomorrow is " & DateAdd(DateInterval.Day,1,Today())
Now() - Returns or sets a Date value containing the current date according to your system.
=Now()
="This time tomorrow is " & DateAdd("d",1,Now())
="This time tomorrow is " & DateAdd(DateInterval.Day,1,Now())
MonthName - Returns a String value containing the name of the specified month.
=MonthName(10,True)
=MonthName(Month(Fields!BirthDate.Value),False)
="The month of your birthday is "&MonthName(Month(Fields!BirthDate.Value))
WeekDayName - Returns a String value containing the name of the specified weekday.
=WeekdayName(2,True,0)
=WeekDayName(DatePart("w",Fields!BirthDate.Value),True,0)
=WeekDayName(DatePart(DateInterval.Weekday,Fields!BirthDate.Value),True,FirstDayOfWeek.System)
Year - Returns an Integer value from 1 through 9999 representing the year.
=Year(Fields!BirthDate.Value)
To format Date values through expressions:
=Format(Today(),"dd-MM-yyyy") --- 23-10-2015
=Format(today(),"dd/MM/yyyy") --- 23/10/2015
=Format(today(),"MMM-dd-yyyy") --- Oct-23-2015
=Format(today(),"MMM-dd-yy") --- Oct-23-15
=FORMAT(Today(),"M/d/yy") ---10/23/15
=FORMAT(Today(),"MM-dd-yyyy") ---10-23-2015
=FORMAT(Today(),"MMM-dd-yyyy") Oct-23-2015
=FORMAT(Today(),"MMMM dd, yyyy") ---October 23, 2015 =FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") ---Oct 23, 2015 01:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") ---Oct 23, 2015 13:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") ---Oct 23, 2015 13:43:33.587
=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") ---Oct 23, 2015 01:43:33 PM
To add/substract date or time(day, month, year, sec etc.) with given date field we can use DateADD function in SSRS expression, which returns a Date value containing a date and time value to which a specified time interval has been added:
=DateAdd(DateInterval.Month, 6, Today())
=DateAdd("d",3,Fields!BirthDate.Value)
=DateAdd(DateInterval.Day,3,Fields!BirthDate.Value)
DateDiff - function returns a Long value specifying the number of time intervals between two Date values.
=DateDiff("yyyy",Fields!BirthDate.Value,"1/1/2010")
=DateDiff(DateInterval.Year,Fields!BirthDate.Value,"1/1/2010")
DatePart - Returns an Integer value containing the specified component of a given Date value.
=DatePart("q",Fields!BirthDate.Value,0,0)
=DatePart(DateInterval.Quarter,Fields!BirthDate.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)
To get first day of current Week:
=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)
To get first day of current Month:
=DateAdd("d",-(Day(today)-1), Today)
=DateSerial( year(today()), month(today()), 1)
