Reporting Services Remove Time from DateTime in Expression

Question

I'm trying to populate an expression (default value of a parameter) with an explicit time. How do I remove the time from the the "now" function?

Solution 1

Found the solution from here

This gets the last second of the previous day:

DateAdd("s",-1,DateAdd("d",1,Today())

This returns the last second of the previous week:

=dateadd("d", -Weekday(Now), (DateAdd("s",-1,DateAdd("d",1,Today()))))

Solution 2

Something like this:

=FormatDateTime(Now, DateFormat.ShortDate) 

Where "Now" can be replaced by the name of the date/time field that you're trying to convert.)
For instance,

=FormatDateTime(Fields!StartDate.Value, DateFormat.ShortDate)

Solution 3

Since SSRS utilizes VB, you can do the following:

=Today() 'returns date only

If you were to use:

=Now() 'returns date and current timestamp

Solution 4

=CDate(Now).ToString("dd/MM/yyyy")

Although you are hardcoding the date formart to a locale.

Solution 5

If you have to display the field on report header then try this... RightClick on Textbox > Properties > Category > date > select *Format (Note this will maintain the regional settings).

Since this question has been viewed many times, I'm posting it... Hope it helps.

View more solutions

Comments

[jasonco]

+1 for the concise answer that, as a matter of fact, was very helpful to me.

[BOORO]

I stumbled here from google.. but.. this doesn't answer your question. (why do you care about the last second?) However, you did beat RSolberg (who correctly answered it). Maybe you should edit your accepted answer to reference his correct answer?

[Valentino Vranken]

DateAdd("s",-1,DateAdd("d",1,Today()) returns the last second of Today, not previous day - and there's a closing backet missing to make it work

[Valentino Vranken]

The question is referring to the default value of a report parameter, so setting the format on a textbox won't work. Also, "D/M/Y" won't give the expected result, try "dd/MM/yyyy" instead.

[ShellNinja]

You are missing the second parameter of the expression FormatDateTime(DateField, DateFormat)

[Nick.McDermaid]

As my date parameter was a datetime, I required an additional CDate() around the above expression.

[donviti]

=Today() does not return date only in SSRS

[Alex]

What if it gets ignored for whatever reason? E.g. date is still shown with hours and minutes. Any idea why this could be happening?

[Wayne]

If you have a function specified for the value of the TextBox/Placeholder then it will override the formatting options on this screen.

[Fandango68]

Won't strip off the time component is the DateColumnName is a DateTime datatype. The OP wants the date only.

[Fandango68]

Won't work if the report is required in Excel format. FormatDateTIme is string function. Sure it strips off the time, but the result is a string date column which when sorting will be sorted as a string from left to right. The user would have to convert the column to a real Date using Text to Columns.

[Fandango68]

As I mentioned above, this works but turns the column into a string. It's still not a true DATE only datatype.

[Fandango68]

The point is, you cannot do what the OP asked in Date only format. You're stuck with string dates.

[amad]

Hi Nathan, Would you please elaborate on your editing ?. sorry i am kind of new to stackoverflow. Regards!

[Rohith]

Saved my day. Event though it is easily visible in the options many wont look here for formatting option. Thanks a lot.

[Chris Mack]

To clarify, Today() returns the date only in terms of value, but the datatype returned is DateTime.

[tdelozie]

This gave me exactly what I needed. I had a DOB column that SSRS assumed needed to add a time to. Microsoft really needs to update this software.