5

I have created an External Content Type through SharePoint Designer. One of the fields that I'm getting from my database is a DateTime column. In SharePoint, however, I want to format this as a Date only. At the moment it's coming through as

2011-05-06 00:00:00z

I want something like:

2011-05-06

Or ideally:

06-05-2011

It sounds really simple, but I can find little information on how to achieve this. Have I missed something really obvious? Is this achievable? I do not want to have to modify the database (that will take time and administration)

EDIT: the particular problem is a date column within the External Data Picker, and the External Content type is only being used for an External Data column.

3 Answers 3

2

There is an article in the DUET info on MSDN that shows you how to do this: http://msdn.microsoft.com/en-us/library/gg271157.aspx

I think you can do it as follows as well (since an external content type is still a content type; however I have not tested this): - Create your external list from the external content type - Go into the external lists you have created for your site in sharepoint designer - In the right hand corner you can see the views. Your external list should have a default view. Click on it and the file should open in sharepoint designer.

Look for a snippet like this:

<XmlDefinition>
            <View Name="{62A0ABCF-DF9D-45DD-985E-46ECDC9F5004}" DefaultView="TRUE" MobileView="TRUE" MobileDefaultView="TRUE" Type="HTML" DisplayName="SmartAppList" Url="/Lists/ghjghj/ReadList.aspx" Level="1" BaseViewID="1" ContentTypeID="0x" ImageUrl="/_layouts/images/generic.png">
                <Method Name="ReadList"/>
                <Query>
                    <OrderBy>
                        <FieldRef Name="Id"/>
                    </OrderBy>
                </Query>
                <ViewFields>
                    <FieldRef Name="Id" ListItemMenu="TRUE" LinkToItem="TRUE"/>
                    <FieldRef Name="Name"/>
                    <FieldRef Name="Description"/>
                    <FieldRef Name="ConfigurationXml"/>
                </ViewFields>
                <RowLimit Paged="TRUE">30</RowLimit>
                <Aggregations Value="Off"/>
                <Toolbar Type="Standard"/>
            </View>
        </XmlDefinition

>

  • At the fieldref for your field you should be able to add the format attribute. This allows you to change the display of the date/time field.

Refer to http://msdn.microsoft.com/en-us/library/aa543225.aspx for possible values.

1
  • Close, but not quite what I was looking for. I'm trying to get the DateTime to appear as a Date only in the External Picker control, and my External Content Type is only being used in an External Data column. This looks good for an External List itself (and is interesting!), but that's slightly different.
    – Andy Burns
    May 11, 2011 at 16:43
2

From SharePoint designer:

  • List and Libraries -> Click on list -> Under Views tab -> Read List
  • Right click on the date field -> Edit formula

    Replace:

    $thisNode/@*[name()=current()/@Name]

    With

    substring-before($thisNode/@*[name()=current()/@Name], ' ')

  • 2
    • Hi and welcome to SP.SE agentc! That's a nice answer. Hope you continue to explore our community. If you need help, don't hesitate to contact me or any other moderator. Cheers!
      – Benny Skogberg
      Nov 15, 2013 at 5:47
    • This worked on SP designer 2010, but I can't seem to get to this on SP designer 2013. The "Design" view appears to be completely missing. Is there a way to do this on SP Designer 2013?
      – russds
      Feb 13, 2015 at 16:39
    0

    It doesn't appear that this is possible at this time, which is annoying. Kudos for Roel for his tip on doing this for an External List.

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

    Not the answer you're looking for? Browse other questions tagged or ask your own question.