Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

External Data - datetime format issue

Posted on 2011-03-17
4
Medium Priority
?
2,112 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
3 Comments
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

578 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