Reporting Services Remove Time from DateTime in Expression

Reporting ServicesExpressionSsrs Expression

Reporting Services Problem Overview


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?

Reporting Services Solutions


Solution 1 - Reporting Services

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 2 - Reporting Services

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 3 - Reporting Services

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

Although you are hardcoding the date formart to a locale.

Solution 4 - Reporting Services

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.

enter image description here

Solution 5 - Reporting Services

Just use DateValue(Now) if you want the result to be of DateTime data type.

Solution 6 - Reporting Services

If expected data format is MM-dd-yyyy then try below,

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

Similarly you can try this one,

=Format(Today(),"MM-dd-yyyy") 

Output: 02-04-2016

Note:
Now() will show you current date and time stamp

Today() will show you Date only not time part.

Also you can set any date format instead of MM-dd-yyyy in my example.

Solution 7 - Reporting Services

In the format property of any textbox field you can use format strings:

e.g. D/M/Y, D, etc.

Solution 8 - Reporting Services

One thing that might help others is that you can place: =CDate(Now).ToString("dd/MM/yyyy") in the Format String Property of SSRS which can be obtained by right clicking the column. That is the cleanest way to do it. Then your expression won't be too large and difficult to visually "parse" :)

Solution 9 - Reporting Services

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 10 - Reporting Services

    FormatDateTime(Parameter.StartDate.Value)

Solution 11 - Reporting Services

I'm coming late in the game but I tried all of the solutions above! couldn't get it to drop the zero's in the parameter and give me a default (it ignored the formatting or appeared blank). I was using SSRS 2005 so was struggling with its clunky / buggy issues.

My workaround was to add a column to the custom [DimDate] table in my database that I was pulling dates from. I added a column that was a string representation in the desired format of the [date] column. I then created 2 new Datasets in SSRS that pulled in the following queries for 2 defaults for my 'To' & 'From' date defaults -

'from'

    SELECT  Datestring
	FROM    dbo.dimDate
	WHERE   [date] = ( SELECT   MAX(date)
					   FROM     dbo.dimdate
					   WHERE    date < DATEADD(month, -3, GETDATE()
                     )

'to'

	SELECT  Datestring
	FROM    dbo.dimDate
	WHERE   [date] = ( SELECT   MAX(date)
					   FROM     dbo.dimdate
					   WHERE    date <= GETDATE()
					 )

Solution 12 - Reporting Services

This should be done in the dataset. You could do this

Select CAST(CAST(YourDateTime as date) AS Varchar(11)) as DateColumnName

In SSRS Layout, just do this =Fields!DateColumnName.Value

Solution 13 - Reporting Services

My solution for a Date/Time parameter:

=CDate(Today())

The trick is to convert back to a DateTime as recommend Perhentian.

Solution 14 - Reporting Services

Just concatenate a string to the end of the value:

Fields!<your field>.Value & " " 'test' 

and this should work!

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJeffView Question on Stackoverflow
Solution 1 - Reporting ServicesMichael MaddoxView Answer on Stackoverflow
Solution 2 - Reporting ServicesRSolbergView Answer on Stackoverflow
Solution 3 - Reporting ServicesPerhentianView Answer on Stackoverflow
Solution 4 - Reporting ServicessinghswatView Answer on Stackoverflow
Solution 5 - Reporting Servicesuser1165019View Answer on Stackoverflow
Solution 6 - Reporting ServicespedramView Answer on Stackoverflow
Solution 7 - Reporting ServiceszzawaidehView Answer on Stackoverflow
Solution 8 - Reporting ServiceslbranjordView Answer on Stackoverflow
Solution 9 - Reporting ServicesJeffView Answer on Stackoverflow
Solution 10 - Reporting ServicesAliviaView Answer on Stackoverflow
Solution 11 - Reporting ServicesChris WoodView Answer on Stackoverflow
Solution 12 - Reporting ServicesRicky HopeView Answer on Stackoverflow
Solution 13 - Reporting Serviceswill websterView Answer on Stackoverflow
Solution 14 - Reporting ServicesamadView Answer on Stackoverflow