Solved

External Data - datetime format issue

Posted on 2011-03-17
4
1,880 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now