Date format in outputted Reporting Services column incorrect

blossompark
blossompark used Ask the Experts™
on
Hi,
I run a query in SSMS and get the following results.
ssms output
I use the exact Query in Visual Studio to create  a Report.
When I deploy the report and run it in Report Manager
I get differences underlined below in the format
 rs outputIt seems strange that some of the formatting is correct.
Any guidance appreciated,
I am using SQL Server 2014
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<Couple of knee-jerk reactions>
  • Is this column a datetime datatype, or some form of character?  Consulting my calendar I'm not seeing a date '22-38-2017'.
  • Show us the SSRS report design view that shows what populates this column, with emphasis on verifying that the column created_date populates it as opposed to something else, and its datetime formatting.
Shaun KlineLead Software Engineer

Commented:
It would appear that you are pulling minute for the month in your dates. I believe that SSRS uses capital M for month and lowercase M for minutes when using a custom date format.

Author

Commented:
Hi Shaun,
this is the code used
 FORMAT(created_date, 'dd-MM-yyyy HH:mm') as  created_date,
 FORMAT(first_ran, 'dd-MM-yyyy HH:mm') as  First_Ran,

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Shaun KlineLead Software Engineer

Commented:
That looks like SQL code. The field in your report will have a format property. What does it have?

Author

Commented:
Hi Jim,
below is Design for these columns, which seems to have no correlation with what format is actually outputted
design

Author

Commented:
Yes Shaun , it is SQL Code, I ran this code in SQL Server management Studio  to test it prior to using as the Data Set for the Report in Visual Studio
Shaun KlineLead Software Engineer

Commented:
If your SQL is using the FORMAT function to format your dates, I suggest changing the property in your screen shot above to General since FORMAT returns an nvarchar data type.

Author

Commented:
Tried that Shaun, still the same result

Author

Commented:
I ticked the box "Use single transaction when processing the queries" in the Data Source Properties and now it is behaving as expected....just luck on my part, more voodoo than logic

Author

Commented:
i just unticked that box and it is working ok still so that was not the reason it fixed itself.... i actually recreated the rdl, that is more likely why it fixed "itself".
still dont understand what the issue was, sometimes I guess you just start again
deleted and recreated the report

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial