Hey everyone, I’m working on a report in SQL Server Reporting Services and I’ve hit a snag. My database has separate numeric fields for month and year. I need to join these fields and format them as MMM-YYYY in my report. For example, I want to turn 7 and 2008 into Jul-2008. I’ve tried a few things but can’t seem to get it right. Any ideas on how to tackle this? I’m pretty new to SSRS so any help would be great. Thanks in advance!
hmm, interesting problem! have u considered using a custom expression in the report itself? something like:
=FORMAT(DATEADD(“m”, Fields!Month.Value - 1, DATEADD(“yy”, Fields!Year.Value - 1900, “1900-01-01”)), “MMM-yyyy”)
might work? what do u think? any other approaches youve tried?
yo, try this in ur query:
STUFF(CONVERT(VARCHAR(7), DATEADD(MONTH, MonthColumn-1, DATEADD(YEAR, YearColumn-1900, 0)), 120), 1, 2, LEFT(DATENAME(MONTH, DATEADD(MONTH, MonthColumn-1, 0)), 3))
it’s a bit messy but gets the job done. lmk if u need help implementing it!
Having worked extensively with SSRS, I can suggest a reliable approach to your problem. In the report’s dataset query, you can use SQL Server’s DATEFROMPARTS function to create a date from your separate month and year fields, then format it as desired. Here’s an example:
CONVERT(VARCHAR(7), DATEFROMPARTS(YearColumn, MonthColumn, 1), 120)
This will give you the ‘MMM-YYYY’ format you’re looking for. If you need further customization, you can use the FORMAT function in newer SQL Server versions. Alternatively, you could handle this in the report itself using a custom expression in a text box. Either way, this should solve your date formatting issue efficiently.