Home > Reporting Services > More on Dates in Reporting Services – DateDiff

More on Dates in Reporting Services – DateDiff


It seems like you always have to perform comparisons with dates to do conditional formatting in Reporting Services. Many of you will know the DateDiff function :


DateDiff("d",Fields!Date1.Value,Fields!Date2.Value)
 

You can also  :

  • Use Parameters or static fields instead of fields from the dataset ( This applies to most functions )
  • Change the "d" ( which means compare days ) to "M" or "yyyy"

If it doesn’t work , remember that the values you are comparing must be date values. Use "CDate" to convert strings to Date values.

And remember , when you want compare a date to todays date , getdate() won’t work, since this is VB Script not SQL !! Use the Now() command as follows :


DateDiff("d",Fields!Date1.Value,Now())
 

If the first date is smaller than the second date , the difference will be a positive number. You need to remember this since normally your expression for conditional formatting is normally something like :


=iif(DateDiff("d",Fields!CalendarDate.Value,Now())>30,"White","Black")
 
Advertisements
Categories: Reporting Services
  1. Todzilla
    February 6, 2014 at 4:46 pm

    You just saved me great time and frustration with this tidbit. Thanks so much!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: