Search code examples
sql-serverreporting

Cannot format datetime in SQL Server Reporting Services report builder

None of the previous questions/answers on this topic are working for me; I'm a bit baffled. I've inherited my first SQL Server Reporting Services Report Builder report and was given the seemingly simple task of changing what was a date to a datetime. As you can see below I've successfully modified the underlying query and am getting back datetime types but I cannot FORMAT them as such on the report.

Results of my query:

enter image description here

My first instinct was that simply changing the type of the column (FirstProcessorCompletedDate) from date to datetime would make cue the report tool in on displaying date and time, but no luck. So...

Previous experience with report building tools told me to take the existing reference to the field and replace it with a format statement for that field, kind of like this:

enter image description here

Unfortunately, whatever I add here is getting interpreted as LITERALS by the report builder upon execution. AND YES, I DID TRY AN "=" IN FRONT OF THE FORMAT STATEMENT. This is just one variation of what I've tried.

enter image description here

Clearly this is not the old SSRS-type report building I am used to. I've Googled the heck out of this and searched Stack Overflow but no joy on the answer. I've looked a little at Calculated Fields but this seems like overkill. I don't want to calculate something, just format it.

What am I missing?

Solution

  • Don't modify the underlying query, that was your first mistake. date(time) data types don't have a format, they are binary values and your presentation layer changes that to a readable format. Once you change the "format" of a date(time) data type in SQL Server it is no longer a date(time) datatype, it's a varchar, and Format does not work on a varchar. How would you format the varchar value 'Hello, my name is Jane.' to the style MM/dd/yyyy hh:mm tt? You can't, and the same applies to a string representing a date.

    Return the value of your column as a datetime to SSRS (don't convert it), and then change the Format Property of your cell in SSRS (hit F4 when you have the cell selected and you'll open up the cell's property pane) and change the format to MM/dd/yyyy hh:mm tt.