DATETIME Field loses time in SSRS, but still listed as datetime

Question

DATETIME Field loses time in SSRS, but still listed as datetime

High Plains Grifter

Ten Centuries

Points: 1191
More actions
June 2, 2014 at 10:09 am

#292009

Hi All,
I have a completely unresolved problem here (three people in our team have looked without solution). In our test server, I made a SSMS stored procedure that returns (among others) a datetime field, which is used in the dataset of a report. I ran the report and saw that the field (CreateDate) displayed correctly as a datetime with the field format of "dd/MM/yyyy HH:mm" as requested in the spec. The report was signed off and added to our live report solution in SSRS, but we soon received a complaint that all the times were showing as 1 o'clock in the morning.
We have a function to convert the database GMT to display BST, which adds one hour to the displayed time at this time of year, so I would expect this result if the SSMS query was finding a date, not a datetime, then adding one hour for BST and giving that to SSRS. This implies that the report is receiving a datetime, as the function is run as part of the SQL query in the stored procedure.
I checked everything I could think of and can see no reason why the test version would return a correct datetime field, while the live version would return this datetime field that looks as though it came from a date field.
In the end, I added a new datasource to the live report, referring back to the test server - the times display correctly, which seems to imply that the problem is somewhere on the server, However, when I run the procedure itself as a query in SSMS, the results include the correct times. I am at a loss to see what is going on here - any suggestions most welcome!
Cheers,
Mark

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Any formatting on the text field? Any special localization of the report?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

We have a function to convert the database GMT to display BST, which adds one hour to the displayed time at this time of year,

BST? Either way, where is this function located? Is it a SSRS function, something inline on the Data Source... ?

This implies that the report is receiving a datetime, as the function is run as part of the SQL query in the stored procedure.

Please confirm the procedure and/or function is actually returning a datetime, not a formatted string, please?

I checked everything I could think of and can see no reason why the test version would return a correct datetime field, while the live version would return this datetime field that looks as though it came from a date field.

Something is apparently different in the environments, which means you need to detail exactly what occurs to this value at each location in its life.

In the end, I added a new datasource to the live report, referring back to the test server - the times display correctly, which seems to imply that the problem is somewhere on the server,

A curious result from that troubleshooting method.

So, only changing the datasource (same proc, same database, same defaults, etc are assumed between the two connections) gives you expected results. Have you force compared the proc versions on the servers? If so, if you can post the code, maybe we can see something wonky that may be happening because of server differences in default SET values.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

High Plains Grifter Ten Centuries Points: 1191 More actions · Answer 3

I have compared the procedures in the live and test versions - apart from the database references (only one line at the top "USE ..."), they are identical. The field in question is a genuine datetime value in all cases and the function that converts GMT to BST when appropriate is on the server, written in SSMS and refferred to in the select statement of the stored procedure. It also returns a datetime, not a string or any other data type. The report in both cases treats the field as a datetime:

<Field Name="ClaimCreateDate">

<DataField>ClaimCreateDate</DataField>

<rd:TypeName>System.DateTime</rd:TypeName>

</Field>

The change of data source results in a change of behaviour; is there something in the background on the server that could result in the change? Some setting or something? Both the test and live servers have the same version on SQL server on them.

Edit:

Sorry, I forgot to mention - the formatting on the field in SSRS is "dd/MM/yyyy HH:mm" and the location is no different to our other reports (which I am now looking through to see if this error slipped through the net any time in the past).

High Plains Grifter Ten Centuries Points: 1191 More actions · Answer 4

Ah, so I have found something that might help shed light: if I run the stored procedure as a query by modifying, then commenting out the "alter proc" to return results in the live environment, the claim create date field is a datetime looking as it should. If I execute the procedure, all the times are returned as 01:00, as the report shows. In other words, the error is only seen when actually executing the procedure.

Thanks for your time!

High Plains Grifter Ten Centuries Points: 1191 More actions · Answer 5

I am sorry.

I have found the error - it is in the source data; someone has changed something somewhere and the more recent claim create dates are entered differently to the elder ones. When checking the table, I used the inbuilt select top 1000 rows function, which only showed me the old values. It appears that there is a database difference between the test and live versions.

Thanks all for your time, but I will be able to continue from here.

Mark