Date format in outputted Reporting Services column incorrect

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
blossomparkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
<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 EngineerCommented:
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.
blossomparkAuthor 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

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Shaun KlineLead Software EngineerCommented:
That looks like SQL code. The field in your report will have a format property. What does it have?
blossomparkAuthor Commented:
Hi Jim,
below is Design for these columns, which seems to have no correlation with what format is actually outputted
design
blossomparkAuthor 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 EngineerCommented:
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.
blossomparkAuthor Commented:
Tried that Shaun, still the same result
blossomparkAuthor 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
blossomparkAuthor 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
blossomparkAuthor Commented:
deleted and recreated the report

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.