Quand on alimente les jalons d’une activité BAM, BizTalk stocke les dates/heures au format UTC (Universal Time Coordinate). Ceci n’est pas gênant tant que l’on utilise le portail BAM. En effet ce dernier effectue la conversion UTC ->heure locale. Cependant on est souvent amené à développer des états reporting services s’appuyant sur les vues exposées dans la base BAM. Dans ce dernier cas la conversion n’est pas effectuée, et on se retrouve donc avec un décalage de une à deux heures (heure d’hivers/heure d’été).

Il est possible en SQL de calculer l’offset courant en utilisant les fonctions DateDiff, GetDate et GetUTCDate mais cette solution n’est pas entièrement satisfaisante puisque le décalage est calculé en se basant sur la date courante : lors de l’analyse de données archivées, on a une chance sur deux que l’heure ainsi calculée soit fausse du fait du décallage entre horaire d’été et horaire d’hivers. (Exemple : requêtage en juillet de données contenant des dates en janvier)

L’idéal serait de disposer en Transact-SQL de fonctions de conversion UTC / Heure locale. Ceci est facile à implémenter à l’aide d’une fonction en code managé :

Pour cela il suffit de créer un projet SQL Server sous visual studio puis de rajouter une fonction utilisateur :

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

public partial class DateConversions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDateTime FromUTCToLocal(SqlDateTime utcdate)

    {

        return new SqlDateTime(utcdate.Value.ToLocalTime());

    }

 

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlDateTime FromLocalToUTC(SqlDateTime localdate)

    {

        return new SqlDateTime(localdate.Value.ToUniversalTime());

    }

};

Il ne reste plus qu’à deployer le projet pour voir apparaitre nos fonctions:

Dernière étape, il faut activer l’éxecution du code .NET (si ce n’est pas déjà fait). Pour ce faire on utilise deux ordres SQL :

sp_configure 'clr enabled',1

reconfigure

 

Il est maintenant possible d’effectuer directement la conversion dans une requête SQL :

select dbo.FromUTCToLocal(Received) Received from bam_ViewCommunications_View where ...

 

Lien de téléchargement du projet

Bon BAM !

Roch