External Data - datetime format issue

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
starkstaringAsked:
Who is Participating?
 
8080_DiverConnect With a Mentor Commented:
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
 
starkstaringAuthor Commented:
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
 
GreatGermCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.