SharePoint Designer: CurrentDate CAMLVariable is incorrect to compare to date column

Question

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?

Answer 1

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].

Answer 2

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/