Solved

External Data - datetime format issue

Posted on 2011-03-17
4
1,953 Views
Last Modified: 2012-11-17
Hi,

I've recently becoming aquinted with SharePoint and now I realise why it takes so many sharepoint consultants to change a light bulb!
Anyway; I have a bit of an issue that I've searched and searched and searched on and cannot figure out why I am getting the issue: before I start, just to clarify, ALL my locals are UK in format.

I'm using an external content type, that is a stored procedure taking data from an employee datawarehouse. It brings back many fields that contain datetime. When I see the External Content type list - I can see the correct datetime fields.

HOWEVER, when I create a custom list using a combination of the ECT and local columns I see the datetime field in the following format

yyy-mm-dd hh:mm:ssZ
2006-10-23 00:00:00Z

As I use these lists to export to Excel for reporting purposes, I need the datetime to be a proper date format (dd/mm/yyyy), hell, I'll settle for something without the Z (I know it's a UTC format).

This is what I've tried to fix it:
I exported the BDC Model, and ammended the XML file to take the local format. (as below)

                          <TypeDescriptor TypeName="System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="Reapproval Date">
                            <Interpretation>
                              <NormalizeDateTime LobDateTimeMode="UTC" />
                            </Interpretation>
                          </TypeDescriptor>

 
Seems to have a lot of these in there, so I modified to:

                          <TypeDescriptor TypeName="System.DateTime" Name="Hire Date">
                            <Interpretation>
                              <NormalizeDateTime LobDateTimeMode="Local" />
                            </Interpretation>
                          </TypeDescriptor>

Reimported this back in. Now my datetimes are correct (I was an hour out on some of them) in terms of the time, but I still get the Z on custom lists.

I decided to run a test (see screenshot), creating a stored proc and selecting different types of datetimes - some varchar conversions (dd/mm/yyyy etc...), some just different datetime formats (I even tried ISO8610 for good measure - ISO datetime format).
The datetimes still have the Z's and the strings are well... strings. Which is fine for formmating purposes (they still output to excel as strings even though they look correct).

I have also tried editing the list in SharePoint designer. Changing the format and doing a substring on the Z) - this works in the list display, however when you output to Excel - bang - you've got the Z again.


Last but not least, I thought about the good (or should that be bad) old calculated column to do a substring of the ECT list: however in the column list on the screen where you enter in the calculation, there aren't the columns from the ECT, only the first column (EMPID) This is where we enter in the employeeID to bring back the information. So I can't do a calculated column either.

Any ideas anybody? Much Appreciated

Kieron  



example.png
test.png
ECT.png
0
Comment
Question by:starkstaring
4 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 500 total points
ID: 35165688
What you are seeing (yyyy-mm-dd hh:mm:ss) is the default representation of the datetime from SQL Server (and most other RDBMS).  That does not reflect how the data is actually stored but, rather, the standard representation for human consumption.  

If you are exporting the data to Excel as a Datetime value, you should be fairly safe; however, if you are exporting it as a string representation of the datetime, then you need to convert the SQL Server datetime value using the CONVERT function in your query extracting the data.  The appropriate CONVERT settings would be:
CONVERT(VARCHAR(20), yourcolumnname, 103)

Open in new window


A link to keep handy is:
MSDN Link for CONVERT
0
 

Author Comment

by:starkstaring
ID: 35173532
The data is being exported as a SQL datetime, but sharepoint is representing it as an ISO datetime format. (with the z). I can change how Sharepoint displays it, but when exporting to Excel, I can't change how it comes out.

When you export to excel, it comes out as 2006-10-23 00:00:00Z
0
 
LVL 17

Expert Comment

by:GreatGerm
ID: 38608622
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question