Home » Sharepoint

Need help with filtering a list based on Date and Time



We are implementing a SharePoint list for our resources to provide information as to whether they will be in the office that day, working from home, etc..


All entries are supposed to be in by 8:30 am and a report is created at that time; however, there are always a few stragglers. 


I would like to create a view which shows all entries which have been created after 8:30 am of the current day.  I have tried the following:



is greater than





is greater than

=TEXT(Sum(DateValue([Today]),TimeValue("8:30 AM")),"m/d/yyyy h:MM:ss")




is greater than

=TEXT(([Today]+TimeValue("8:30 AM")), "m/d/yyyy h:MM:ss")




is greater than

=[Today]+TimeValue("8:30 AM")


They all result in “Filter value is not in a supported date format.”



SharePoint Designer is not supported for our SharePoint sites so I can not modify the CAML query to include IncludeTimeValue=”TRUE” for  [Today] (and I am not sure it would help me anyway).

Anyone have any suggestions for me?



1 Answer Found


Answer 1

Working with time/dates in SharePoint columns can be tricky. Check this post out and try using the calculated column method described.

Another good source is a seven-part series Dessie Lunsford wrote about calculated columns and working with date and time.




I have a calendar view that I need to filter by start date based on a user submitted value.

I have tried the solution here: http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/081bc1c1-7a91-4aa2-81e7-a7ce2e880760,
which involved creating a new column in the content type with a calculated value based off of the Start Time, but it doesn't work and I can't seem to figure out why.

Upon successfully connecting the filter to the view and specifying a date, no rows are being displayed when they should.

That being said, if I set the filter to a null value - I CAN filter by using the dropdown menu on the Start Date column itself, so I know that A) The column is calculated correctly and B) The values do exist.

Thank you!


Hi - I am unable to filter a Documents list, in my team site, by date.

I have a bunch of folders and files from the last few years in there and I would like to hide everything EXCEPT ones within the last 60 days.

I would prefer this to be filtered by the [Modified] column that is displayed in my list view, however when I go to filter that column is not listed as an option. Neither is [Created On].

Any suggestions?
I've already tried making a calculated column with the Today trick, but that kept popping errors and also is static, so less than ideal.



I have a process that writes xml files to a share. BizTalk has a couple of subscribers to this file. I need to enhance some functionality here by promoting a datetime value within the file. Only if the current date is greater than the promoted value should the file be processed.

Any ideas?




Creating a report in visual studios and want to filter out data based on a date field. Want to show the post that are 3 months back in time according to my date field.





I would like to create a view to a document library that is filtered by documents added to the library within the last 30 days. I was hoping this could be done by adding a condition to filter by a date modifed greater than [Today] - 30. However, I am getting an error for this.

Does anyone know how to do this?




I have a gridview, based on thsi datasource:


<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">

Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:


<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr WHERE (tblDeliveredInfo.deliveryInfo_FirmaNavn = @firmanavn) ORDER BY delivered_Date DESC">
            <asp:ControlParameter ControlID="DropDownListKunder" Name="firmanavn" 
                PropertyName="SelectedItem.Text" />


Now, using a textbox and a ajax calendar, I would like to be able to filter on the date as well, but my datasource so far:


<asp:SqlDataSource ID="SqlDataSourceDatoLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr WHERE (DATEADD(day, 0, DATEDIFF(day, 0, tblDeliveredInfo.deliveryInfo_Dato)) = @dato) ORDER BY deliveryInfo_FirmaNavn ">
            <asp:ControlParameter ControlID="txtStartDate" Name="dato" 
                PropertyName="Text" Type="DateTime" />


doesn't  work very good. In some case sit doesnt show anything, in onther situations - with 10 records for a given date, it only shows one.



Question one: How do I make the last datasource work? Its probably - again-again - something with the datetime formats, but for the life of me, I can't see how to do.


Question two: The Ideal solution would be using the first datasource, SqlDataSourceAllLoads, and then be able to filter the results with filterexpressions/parameters, but again, how? The filterparameters would origin from a DropDownListKunder, matching the tblDeliveredInfo.deliveryInfo_FirmaNavn as seen in the second datasource, while the date comes from the textbox txtStartDate (databinding fired by a button), but is that possible? Probably yes...?


I am linking (merging) multiple document libraries across a site collection and displaying the results in a data view webpart on our home page, in hopes of being able to display newly uploaded documents (documents uploaded today or within the past 48hrs - I am still awaiting the specific requirements for this).

At this time, I have linked 3 document libraries with the columns (Name, Created By, and Created) and displayed them in a data view webpart. I have tried to filter based on Created = Today and have tried numerous options with no luck. The webpart is just not showing any data. I hope someone can help with this issue. -Also I'm open to hearing any other suggestions on how to do this display this information on our homepage. Thank you!!



I work on a team that routinely needs to run a network trace tool 24x7 until an issue occurs. To limit size of the files we currently only log to a single file of 500MB. We don't want to use chained files because we have not identified a way to limit the total size of all captures combined. 


500MB capture files are slow to work with. 


Is there a way to filter a capture file based on time span? For example, if I have a 500MB trace that runs from 5/5/10 00:00:00 through 5/7/10 12:00:00 but I only want the frames from 5/6/10 16:40:00 through 5/6/10 17:00:00 - what filter can I use (if any)? Is there a way to limit the total size of all captures specified? For example if I want to log  50 capture files of 10MB each and overwrite the the oldest log file once all files combined equal 500MB.

I have a custom Data View Web Part I have developed using SharePoint Designer.  One of the main Lists is a Calendar List.  The main filter needs to filter the [Start Date] <= Current Date and TIME and [End Date] >= Current Date and TIME.  The problem is it is not comparing against the current date and TIME at all.  It seems to be filtering out anyting that is within a 24 hour window of the two fields.  But I need it to filter out anything that is not within the Start Time and End Time down to the CURRENT TIME.

I understand that [Start Time] is really @EventDate.  But I don't know how to get it to compare to the exact current TIME.

The Filter Criteria it creates is shown below. 

[contains(@FieldName1, $dvt_ParentRow/@Title) and number(translate(substring-before(@EventDate,'T'),'-','')) <= number(translate(substring-before($Today,'T'),'-','')) and number(translate(substring-before(@EndDate,'T'),'-','')) >= number(translate(substring-before($Today,'T'),'-',''))]


I'm trying to filter  date and time

a litlle help is welcome

this is what i have so far



startTD AsDate = Dtp1.Value.Date

objbindingsource.Filter =

"Datum > = '" & startTD & "'"


Hi Experts,

How can I include a date time filter like following ?

([Event_Date] + [Event_Time]) >= yesterday 00:30:00 AND ([Event_Date] + [Event_Time]) <= Today 01:00:00

Event_Date and Event_Time columns are in datetime format





I have a little question about calculated columns in WSS 3.0 (not MOSS 2007)

How can I have a calculated column which is a Date+Time based on a text_column

Converting YYYYMMDD_HHMM (time = 24 hour format) format  ==> Date+Time


20070629_0848 converting to 29/06/2007 08:48

==> I have already a formule, but with teh DATE(...) function, you cannot give the hour-minute-seconds as a parameter, so the time is always 00:00 in WSS 3.0 (and yes, i have selected date+time as result-type for the column)

Is this possible in WSS to calculate a full date out of a text, maybe if i use two seperate text-columns, and convert a to date and one to time and a third which combines the two...

It would be a huge help if somebody could give me som advice to calculate a full date+time out of one or more text-columns....



Hello.  I am working on a task order project where monitors are allowed to see all task orders assigned to them.  They should only see their own task orders.  In the same way, Section Managers should see all task orders within their section.  Finally, Division Managers should see all task orders within their division.  There needs to be some type of hierarchical relationship.  Can someone please advise me how to do this and to filter for the correct records depending upon who is logged in.  Thank you.
In an attempt to do a list filter via a view in SP 2003, using date "Created" greater than or equal to [Today] - 30 days). For some reason the dailog returns an invalid field. Yet in SP 2007 this sam logic works fine.  Does anyone have an idea of an alternative? 


I have designed a Quiz based on http://blogs.technet.com/b/brenclarke/archive/2009/04/14/creating-a-quiz-web-application-using-sharepoint-designer.aspx. In default.aspx where I have designed quiz list from where the users would attempt to take a quiz. My requirment is once the user has taken the quiz 'A' then he shouldn't get back to the same page and attempt to take the same quiz again. How should I do this. Present default.aspx page is displaying all the quiz list items. Quiz is designed in Quiz list, Questions are designed in Questions list, Answers are designed in Answers list and Iam taking report from answers list and putting the report in QuizReport list with Quiz name, username and no. of correct answers. How do I filter with quiz name, username from QuizReport with Quiz name and logged in name in default.aspx page.


Thanks in advance.





The following is the code in the DataFormWebPart

<DataSources><SharePoint:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" UseServerDataFormat="true" selectcommand="&lt;View&gt;&lt;ViewFields&gt;&lt;FieldRef Name=&quot;ContentTypeId&quot;/&gt;&lt;FieldRef Name=&quot;Title&quot;/&gt;&lt;FieldRef Name=&quot;File_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;RoutingEnabled&quot;/&gt;&lt;FieldRef Name=&quot;Category&quot;/&gt;&lt;FieldRef Name=&quot;Approach&quot;/&gt;&lt;FieldRef Name=&quot;Channel&quot;/&gt;&lt;FieldRef Name=&quot;Competency_x0020_Pillar&quot;/&gt;&lt;FieldRef Name=&quot;Contact&quot;/&gt;&lt;FieldRef Name=&quot;Cost&quot;/&gt;&lt;FieldRef Name=&quot;Cost_x0020_Description&quot;/&gt;&lt;FieldRef Name=&quot;Country&quot;/&gt;&lt;FieldRef Name=&quot;Editor_x0027_s_x0020_choice&quot;/&gt;&lt;FieldRef Name=&quot;Execution_x0020_Plan&quot;/&gt;&lt;FieldRef Name=&quot;External_x0020_Links&quot;/&gt;&lt;FieldRef Name=&quot;Issue&quot;/&gt;&lt;FieldRef Name=&quot;Lessons&quot;/&gt;&lt;FieldRef Name=&quot;Market_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;Next_x0020_Steps&quot;/&gt;&lt;FieldRef Name=&quot;Practice_x0020_Type&quot;/&gt;&lt;FieldRef Name=&quot;Quarter&quot;/&gt;&lt;FieldRef Name=&quot;Region&quot;/&gt;&lt;FieldRef Name=&quot;Relevant_x0020_Documents&quot;/&gt;&lt;FieldRef Name=&quot;Results&quot;/&gt;&lt;FieldRef Name=&quot;Solution&quot;/&gt;&lt;FieldRef Name=&quot;Start_x0020_Date&quot;/&gt;&lt;FieldRef Name=&quot;Submitted_x0020_By&quot;/&gt;&lt;FieldRef Name=&quot;Supporting_x0020_Documents&quot;/&gt;&lt;FieldRef Name=&quot;Active&quot;/&gt;&lt;FieldRef Name=&quot;Original_x0020_Modified&quot;/&gt;&lt;FieldRef Name=&quot;Original_x0020_Modified_x0020_By&quot;/&gt;&lt;FieldRef Name=&quot;Original_x0020_Created&quot;/&gt;&lt;FieldRef Name=&quot;Original_x0020_Created_x0020_By&quot;/&gt;&lt;FieldRef Name=&quot;WBGConfi&quot;/&gt;&lt;FieldRef Name=&quot;ID&quot;/&gt;&lt;FieldRef Name=&quot;Modified&quot;/&gt;&lt;FieldRef Name=&quot;Created&quot;/&gt;&lt;FieldRef Name=&quot;Author&quot;/&gt;&lt;FieldRef Name=&quot;Editor&quot;/&gt;&lt;FieldRef Name=&quot;_HasCopyDestinations&quot;/&gt;&lt;FieldRef Name=&quot;_CopySource&quot;/&gt;&lt;FieldRef Name=&quot;_UIVersion&quot;/&gt;&lt;FieldRef Name=&quot;_UIVersionString&quot;/&gt;&lt;FieldRef Name=&quot;Attachments&quot;/&gt;&lt;FieldRef Name=&quot;_ModerationStatus&quot;/&gt;&lt;FieldRef Name=&quot;FileRef&quot;/&gt;&lt;FieldRef Name=&quot;FileDirRef&quot;/&gt;&lt;FieldRef Name=&quot;Created_x0020_Date&quot;/&gt;&lt;FieldRef Name=&quot;FSObjType&quot;/&gt;&lt;FieldRef Name=&quot;FileLeafRef&quot;/&gt;&lt;FieldRef Name=&quot;ItemChildCount&quot;/&gt;&lt;FieldRef Name=&quot;FolderChildCount&quot;/&gt;&lt;FieldRef Name=&quot;PermMask&quot;/&gt;&lt;/ViewFields&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name=&quot;ID&quot;/&gt;&lt;Value Type=&quot;Counter&quot;&gt;{PracID}&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="dataformwebpart1" Scope="Recursive"><SelectParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{6A068552-4B26-46DF-AEA3-3323CE4C8A21}"/><asp:Parameter Name="StartRowIndex" DefaultValue="0"/><asp:Parameter Name="nextpagedata" DefaultValue="0"/><asp:Parameter Name="MaximumRows" DefaultValue="10"/></SelectParameters><DeleteParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{6A068552-4B26-46DF-AEA3-3323CE4C8A21}"/></DeleteParameters><UpdateParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{6A068552-4B26-46DF-AEA3-3323CE4C8A21}"/></UpdateParameters><InsertParameters><WebPartPages:DataFormParameter Name="ListID" ParameterKey="ListID" PropertyName="ParameterValues" DefaultValue="{6A068552-4B26-46DF-AEA3-3323CE4C8A21}"/></InsertParameters></SharePoint:SPDataSource></DataSources>

   <ParameterBinding Name="dvt_apos" Location="Postback;Connection"/>
   <ParameterBinding Name="ManualRefresh" Location="WPProperty[ManualRefresh]"/>
   <ParameterBinding Name="UserID" Location="CAMLVariable" DefaultValue="CurrentUserName"/>
   <ParameterBinding Name="Today" Location="CAMLVariable" DefaultValue="CurrentDate"/>
   <ParameterBinding Name="dvt_startposition" Location="Postback" DefaultValue=""/>
   <ParameterBinding Name="dvt_firstrow" Location="Postback;Connection"/>
   <ParameterBinding Name="dvt_nextpagedata" Location="Postback;Connection"/>
   <ParameterBinding Name="ListID" Location="None" DefaultValue="{6A068552-4B26-46DF-AEA3-3323CE4C8A21}"/>
   <ParameterBinding Name="StartRowIndex" Location="None" DefaultValue="0"/>
   <ParameterBinding Name="nextpagedata" Location="None" DefaultValue="0"/>
   <ParameterBinding Name="MaximumRows" Location="None" DefaultValue=""/>
   <ParameterBinding Name="PracID" Location="QueryString(id)" DefaultValue=""/>

But it does not filter the list data and always gives there are no items to display in this mode

Hello. I have a DVWP that is constructed using a "linked data source" (one custom list and one calendar list).

The DVWP is displaying the information correctly using a joined sub-query. The sub-query displays data from the calendar list and I want to filter those entries using a date range provided by the end user. I'm attempting to use the SP DateTimeControl, creating parameters referencing those controls, and then applying a filter using the parameters.

When applying the filters, everything is filtered regardless of the data submitted using the controls. Would anyone be able to point me in the right direction on how to apply this filter?



I have an specific list on my site and this list has data from different customers, internally my users need to see all the existing data without any limitation but the same list is exposed to my customers, they log to sharepoint using FBA and now my challenge to limit their visibility to the list items according to their profile. In other words what I need to adapt is the filter to my list based on the FBA user according to their respective customer.


Right now on the FBA database I added a new column called "ID" on my aspnet_Users table, this new column identifies the customer ID and I what I'm trying to do is to use this "ID" as my filter, so far I haven't found a way to expose this database column to a sharepoint list using the UI, I have no idea if I can do this directly without writing some specific code/solution, actually I have no idea where to look for, I did try a lot of different approaches and so far I see no solution to my problem.


Is there any way to address this problem?


Hi All,

   I am using a 'choice filter web part' and a 'Share Point List Filter web part' on a page to filter other web parts ( Lists / Data view web parts).

   The filters are working fine, but once a filter is selected there doesn't seem to be a way of showing all contents back ( that is clearing the selected filter) . The selected filter remains even if we refresh the page !!!

 Any suggestions as on how we can have the option of 'show all' or 'clear filter' for  Choice Filter web part .


I am using SharePoint  Form Generator web part http://sfg.codeplex.com/ to create new item form to insert items to the list. It works pretty good EXCEPT it doesnt let me save date input to the list. It always says it as todays date!!  anyone using this web part and saving dates?


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure