SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


Or ideally:


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.

share|improve this question

There is an article in the DUET info on MSDN that shows you how to do this:

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:

            <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"/>
                        <FieldRef Name="Id"/>
                    <FieldRef Name="Id" ListItemMenu="TRUE" LinkToItem="TRUE"/>
                    <FieldRef Name="Name"/>
                    <FieldRef Name="Description"/>
                    <FieldRef Name="ConfigurationXml"/>
                <RowLimit Paged="TRUE">30</RowLimit>
                <Aggregations Value="Off"/>
                <Toolbar Type="Standard"/>


  • 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 for possible values.

share|improve this answer
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 '11 at 16:43
Thanks buddy, this suggestion works for me! :) – user13263 Nov 28 '12 at 14:11

From SharePoint designer:

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




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

  • share|improve this answer
    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 '13 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 '15 at 16:39
    up vote 0 down vote accepted

    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.

    share|improve this answer

    Your Answer


    By posting your answer, you agree to the privacy policy and terms of service.

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