Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am creating a simple form by "Insert Data View" web part and filtering on the data source by date.

Something like: Displays all the announcement items that have:

"Expires" Greater Than Or Equal [Current Date]

This seem easy by using the filter task of the Data form Web Part.

I didn't even look back until some body complains the announcements for today does not seem to appears as expected.

Turn out the [Current Date] variable I was selecting comes from a CAMLVariable named Today, which when printed out is local time.

This, when compare with the Expires date column, which when printed out is Zulu time.

Obviously, the Expires and Current Date, when compared will be off by however many hours the local time is compared to Zulu time.

To get around this issue, I can't have to use a different parameter for comparison, one that is correct today Zulu time.

I created an ASP.NET web control to output the today Zulu time, which then will be used as parameter to the Data form Web Part filter.

The web control code is as follow:

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Text
Imports System.Web
Imports System.Web.UI

<DefaultProperty("Text"), ToolboxData("<{0}:TodayAtZulu runat=""server"" id=""TodayAtZulu""></{0}:TodayAtZulu>")> _
<Description("Get text of Today at Universal (Zulu) time.")> _
Public Class TodayAtZulu
    Inherits System.Web.UI.Control

    <Category("Value")> _
    Public ReadOnly Property Text() As String
        Get
            Return Microsoft.SharePoint.Utilities.SPUtility.FormatDate(Microsoft.SharePoint.SPContext.Current.Web, System.DateTime.Now.ToUniversalTime, Microsoft.SharePoint.Utilities.SPDateFormat.ISO8601)
        End Get
    End Property

End Class

After deploying the control on the server, adding it to the form and using the control text as a parameter for filtering, all announcements result turn back correctly.

My question is: Is there a more elegant way?

share|improve this question
add comment

2 Answers

And there is a more elegant solution:

Correct the application time zone setting. Central Admin --> Web application general settings --> Select time zone --> Choose the correct local time zone.

After this, the date column's value should be in local time and is compatible for comparison with the [Current Date].

share|improve this answer
    
I had a chance to come back to this topic and do more digging. I could not be more wrong in the this answer. Read more in the following answer for a better picture. –  Tinminator Jun 15 '12 at 1:48
add comment
up vote 0 down vote accepted

After many trial with the theory that the [Current Date] parameter value is offset to local time incorrectly by SharePoint, I finally found out the case is not so.

When constructing filter for DataformWebPart involving the date comparison like so:

@Start Less Than Or Equal [Current Date]

My SQL profiler shows the condition of SQL query from SharePoint to database as:

UserData.[datetime2] <= '2012-06-14 14:00:00' 

Notice the time component is 14:00 hour?

My timezone is 10+, which mean time component of [Current Date] is zero out and offset to zulu time (24-10=14) when it reachs SQL layer.

Other date time parameter passed in is also treated this way.

Which mean the solution of adding a custom web control to output the zulu time (as stated in the question) would not yield correct result.

The best course of action is not to rely on default filter of DataformWebPart to get final result, but also use an additional XSLT filter of the same web part to do date comparison. For more info of how it's done, see http://blog.sharepointrx.com/2010/12/03/conditional-formatting-and-date-comparisons-with-sharepoint-designer-2010/

share|improve this answer
add comment

Your Answer

 
discard

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.