Last updated on September 28th, 2022 at 09:36 am
The idea for this blog post was on my list of “things to do” for quite a while. Why? Again, and again, I saw comments such as, “My date is in the wrong format,” and, “I just want the short date, I don’t need the time.” There are numerous ways to solve these problems, but which one is the best? How should I answer? In the end, I decided to talk about three of the most common ways you can display a date, in short form, in an SSRS report. The point of this post is NOT to tell you how to create a short date, BUT to show you what options exist.
These are the three ways I’ll be discussing:
- SQL Server Query
- SSRS Functions
- Text Box Properties
Becoming an expert on how each of these items work when formatting columns enables you to get the most out of your reports and teaches you how to avoid the “gotchas.” It’s incredible how you can format just about anything within your ConfigMgr reports, including how to display a date in a text box.
Before Getting Started
I just want to emphasize that this is NOT going to be a step-by-step guide. Basically, this is an introduction into how to use each of the three items, I listed above, to format your columns. Most of my tips and tricks should be easy to implement, but if you have any questions, please reach out to me. They all work in BOTH Visual Studio and Report Builder.
Display a Date – Example Query
It is always easier to explain something when you use an example, so below is a simple query. It shows the computer name and the date it was added to ConfigMgr. By the way, I created a ZIP file with this example RDL. You can download and review it if you want to see the difference between each method. The link for it is near the end of this blog post.
Select
RV.Netbios_Name0 as ‘Computer’,
RV.Creation_Date0 as ‘Creation Date’
from
dbo.v_R_System_Valid RV
Order by
RV.Netbios_Name0
Results
Above is a screenshot of what the results in my lab look like in SQL Server Management Studio (SSMS).
SQL Server Query – CONVERT and FORMAT Functions
Now, I can’t possibly show you every single combination you could use to format the results within a SQL Server query, but it is possible if I use the example from above. You can change the date to a short date by using one of these two SQL Server functions: CONVERT Function or FORMAT Function (see below).
convert(nvarchar(10), RV.Creation_Date0, 23) as ‘Convert’,
format(RV.Creation_Date0,’yyyy-MM-dd’) as ‘Format’
SSRS Date Functions
Again, I can’t emphasize it enough, there is more than one way to display a date. In this example, after creating a basic SSRS report, I add a table with the SQL Server query listed above. You can see the Expression value in the screenshot below. This value produces the final results that appear in the report screenshot at the end of this post.
In the Expression box, you can select the FormatDateTime (.net Doc link) and wrap the date column using this function. OR, you can use the SSRS Format string function. That’s what I did within the SQL Server query. The syntax is basically the same.
Text Box Properties
Text Box properties is the last item I want to tell you about. You can see in the screenshot below that you can change the date into a short format. Notice, however, that a few of these options have an asterix “*” in front of them which means that the regional setting comes into effect. What are the implications of that? The date is displayed based on the regional setting of the server hosting SSRS.
Final Results
Above is the report I produced using the three methods I talked about in this post. Taking a look at the final results, do you notice that the SSRS Date Function results use the regional setting of the hosting server and matches it in the short form date? Is that a bad thing to have the date displayed in the regional format? No, it is not a bad thing, in fact, it is a good thing. Just be careful, though, because in an international company does 2/7/2006 mean February 7, 2006 or July 2, 2006? This is one of the reasons why I prefer to use 2006-02-07 because it is always understood to mean February 7, 2006.
Download
As promised, here is the link to download the RDL and SQL Server query example I used in this blog post: https://www.enhansoftdownloads.com/misc/DateFormat.zip.
What About Power BI?
This is great question. Power BI does a lot to be, “helpful,” BUT in an international company, some of the helpful things aren’t so helpful. For example, short dates are automatically converted to Date/Time. I asked Enhansoft’s Power BI Report Developer, Leonard Suares, to cover this topic when he has a chance, but it is on his very long backlog list. Do you want him to cover it sooner rather than later? Tweet at him and tell him how important it is to you! The more people that want to see this post, the sooner it gets done.
If you have any questions about the three methods I just showed you on how to display a date in SSRS reports, please feel free to contact me @GarthMJ.