Solved

External Data - datetime format issue

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

13 Experts available now in Live!

Get 1:1 Help Now