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`
<Interpretation>
<NormalizeDateTime LobDateTimeMode="UTC" />
</Interpretation>
</TypeDescriptor>
Seems to have a lot of these in there, so I modified to:
<TypeDescriptor TypeName="System.DateTime"
<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
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`
<Interpretation>
<NormalizeDateTime LobDateTimeMode="UTC" />
</Interpretation>
</TypeDescriptor>
Seems to have a lot of these in there, so I modified to:
<TypeDescriptor TypeName="System.DateTime"
<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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
When you export to excel, it comes out as 2006-10-23 00:00:00Z