Public Function ConvertUtcTimeToLocalTime(ByVal UtcDateTime As Date) As Date ' L'heure fournie est exprimée en UTC et au format du système ' L'heure renvoyée sera exprimée en heure locale, et toujours au format du système ' Accepte n'importe quel format de date du moment que ce format est le format de l'OS Dim tzi As TIME_ZONE_INFORMATION Dim UtcTime As SYSTEMTIME Dim LocalTime As SYSTEMTIME Call GetTimeZoneInformation(tzi) With UtcTime .wDay = Day(UtcDateTime) .wMonth = Month(UtcDateTime) .wYear = Year(UtcDateTime) .wHour = Hour(UtcDateTime) .wMinute = Minute(UtcDateTime) .wSecond = Second(UtcDateTime) End With Call SystemTimeToTzSpecificLocalTime(tzi, UtcTime, LocalTime) With LocalTime ConvertUtcTimeToLocalTime = CDate(DateSerial(.wYear, .wMonth, .wDay) & _ " " & _ TimeSerial(.wHour, .wMinute, .wSecond)) End With End FunctionBien sûr, il faut ajouter les déclarations qui vont bien, mais comme je te l'ai déjà dit, ces exemples existent dans http://logiciel.codes-sources.com/logiciels/API-Guide-197.aspx + un tout petit peu de temps pour comprendre et faire des essais
UtcDateTime = (extraite de la BDD) TempsLocal = ConvertUtcTimeToLocalTime(ByVal UtcDateTime As Date) As Date
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questiondebHiver = DateSerial(yy,mm,dd) * 1 + TimeSerial(h,m,s) * 1
' Convert a Date into a SYSTEMTIME. Private Sub DateToSystemTime(ByVal the_date As Date, ByRef _ system_time As SYSTEMTIME) With system_time .wYear = Year(the_date) .wMonth = Month(the_date) .wDay = Day(the_date) .wHour = Hour(the_date) .wMinute = Minute(the_date) .wSecond = Second(the_date) End With End Sub ' Convert a SYSTEMTIME into a Date. Private Sub SystemTimeToDate(system_time As SYSTEMTIME, _ ByRef the_date As Date) With system_time the_date = DateSerial(.wYear, .wMonth, .wDay) + _ TimeSerial(.wHour, .wMinute, .wSecond) End With End Sub ' Convert a UTC time into local time. Private Function UTCToLocalTime(ByVal the_date As Date) As _ Date Dim system_time As SYSTEMTIME Dim local_file_time As FILETIME Dim utc_file_time As FILETIME ' Convert it into a SYSTEMTIME. DateToSystemTime the_date, system_time ' Convert it to a UTC time. SystemTimeToFileTime system_time, utc_file_time ' Convert it to a FILETIME. FileTimeToLocalFileTime utc_file_time, local_file_time ' Convert it to a SYSTEMTIME. FileTimeToSystemTime local_file_time, system_time ' Convert it to a Date. SystemTimeToDate system_time, the_date UTCToLocalTime = the_date End Function
Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" ( _ TimeZone As TIME_ZONE_INFORMATION, _ tempsUTC As SYSTEMTIME, _ tempsLOCAL As SYSTEMTIME _ ) As Long
Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" ( _ TimeZone As TIME_ZONE_INFORMATION, _ tempsUTC As SYSTEMTIME, _ tempsLOCAL As SYSTEMTIME _ ) As Long Public Function ConvertUtcTimeToLocalTime(ByVal UtcDateTime As Date) As Date ' L'heure fournie est exprimée en UTC et au format du système ' L'heure renvoyée sera exprimée en heure locale, et toujours au format du système ' Accepte n'importe quel format de date du moment que ce format est le format de l'OS Dim tzi As TIME_ZONE_INFORMATION Dim UtcTime As SYSTEMTIME Dim LocalTime As SYSTEMTIME Call GetTimeZoneInformation(tzi) With UtcTime .wDay = Day(UtcDateTime) .wMonth = Month(UtcDateTime) .wYear = Year(UtcDateTime) .wHour = Hour(UtcDateTime) .wMinute = Minute(UtcDateTime) .wSecond = Second(UtcDateTime) End With Call SystemTimeToTzSpecificLocalTime(tzi, UtcTime, LocalTime) With LocalTime ConvertUtcTimeToLocalTime = CDate(DateSerial(.wYear, .wMonth, .wDay) & _ " " & _ TimeSerial(.wHour, .wMinute, .wSecond)) End With End Function
Private Type TIME_ZONE_INFORMATION Bias As Long StandardName(32) As Integer StandardDate As SYSTEMTIME StandardBias As Long DaylightName(32) As Integer DaylightDate As SYSTEMTIME DaylightBias As Long End Type Private Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type ' http://msdn.microsoft.com/en-us/library/ms724421%28VS.85%29.aspx Private Declare Function GetTimeZoneInformation Lib "Kernel32" (ByRef TimeZoneInfo As TIME_ZONE_INFORMATION) As Long ' http://msdn.microsoft.com/en-us/library/ms724949%28v=VS.85%29.aspx Private Declare Function SystemTimeToTzSpecificLocalTime Lib "Kernel32" (ByRef TimeZoneInfo As TIME_ZONE_INFORMATION, ByRef UTC_Time As SYSTEMTIME, ByRef Local_Time As SYSTEMTIME) As Long
Dim maDate As Date maDate = Now MsgBox "Heure UTC : " & ConvertUtcTimeToLocalTime(maDate)
' http://msdn.microsoft.com/en-us/library/ms725485%28v =VS.85%29.aspx Private Declare Function TzSpecificLocalTimeToSystemTime Lib "Kernel32" (ByRef TimeZoneInfo As TIME_ZONE_INFORMATION, ByRef Local_Time As SYSTEMTIME, ByRef UTC_Time As SYSTEMTIME) As Long
Public Function ConvertLocalTimeToUtcTime(ByVal LocalDateTime As Date) As Date ' L'heure fournie est exprimée en heure locale et au format du système ' L'heure renvoyée sera exprimée en UTC, et toujours au format du système ' Accepte n'importe quel format de date du moment que ce format est le format de l'OS Dim tzi As TIME_ZONE_INFORMATION Dim UtcTime As SYSTEMTIME Dim LocalTime As SYSTEMTIME Call GetTimeZoneInformation(tzi) With LocalTime .wDay = Day(LocalDateTime) .wMonth = Month(LocalDateTime) .wYear = Year(LocalDateTime) .wHour = Hour(LocalDateTime) .wMinute = Minute(LocalDateTime) .wSecond = Second(LocalDateTime) End With Call TzSpecificLocalTimeToSystemTime(tzi, LocalTime, UtcTime) With UtcTime ConvertLocalTimeToUtcTime = CDate(DateSerial(.wYear, .wMonth, .wDay) & _ " " & _ TimeSerial(.wHour, .wMinute, .wSecond)) End With End Function
Private Const LOCALE_SYSTEM_US As Long = 1033 Private Const LOCALE_SYSTEM_FRENCH As Long = 1036 Private Const LOCALE_SYSTEM_JAPAN As Long = 1041 Private Const LOCALE_NOUSEROVERRIDE As Long = &H80000000 ' http://msdn.microsoft.com/en-us/library/dd318086%28VS.85%29.aspx Private Declare Function GetDateFormat Lib "Kernel32" Alias "GetDateFormatA" (ByVal Locale As Long, ByVal dwFlags As Long, lpDate As SYSTEMTIME, ByVal lpFormat As String, ByVal lpDateStr As String, ByVal cchDate As Long) As Long
Public Function FormatSQLDateTimeString(OsFormatedDate As Date) As String ' Convertit une date/heure en date générale internationale (US) : Mois/Jour/Année ' Accepte n'importe quel format de date du moment que ce format est le format de l'OS Dim mDate As SYSTEMTIME Dim sBuffer As String Dim r As Long With mDate .wDay = Day(OsFormatedDate) .wMonth = Month(OsFormatedDate) .wYear = Year(OsFormatedDate) .wHour = Hour(OsFormatedDate) .wMinute = Minute(OsFormatedDate) .wSecond = Second(OsFormatedDate) End With ' Date sBuffer = String(255, 0) r = GetDateFormat(LOCALE_SYSTEM_US, DATE_SHORTDATE, mDate, vbNullString, sBuffer, Len(sBuffer)) If r > 0 Then FormatSQLDateTimeString = Left$(sBuffer, r - 1) Else Debug.Print Time, "(FormatSQLDateTimeString - date) La fonction renvoie " & CStr(r) End If ' Heure sBuffer = String(255, 0) r = GetTimeFormat(LOCALE_SYSTEM_US, LOCALE_NOUSEROVERRIDE, mDate, vbNullString, sBuffer, Len(sBuffer)) If r > 0 Then FormatSQLDateTimeString = FormatSQLDateTimeString & " " & Left$(sBuffer, r - 1) Else Debug.Print Time, "(FormatSQLDateTimeString - heure) La fonction renvoie " & CStr(r) End If End Function
Private Type SYSTEMTIME wYear As Integer wMonth As Integer wDayOfWeek As Integer wDay As Integer wHour As Integer wMinute As Integer wSecond As Integer wMilliseconds As Integer End Type Private Type TIME_ZONE_INFORMATION Bias As Long StandardName(0 To 31) As Integer StandardDate As SYSTEMTIME StandardBias As Long DaylightName(0 To 31) As String * 64 DaylightDate As SYSTEMTIME DaylightBias As Long End Type Private Declare Function SetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long Private Declare Function GetTimeZoneInformation Lib "kernel32" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long Private Declare Function SystemTimeToTzSpecificLocalTime Lib "kernel32" ( _ TimeZone As TIME_ZONE_INFORMATION, _ tempsUTC As SYSTEMTIME, _ TempsLocal As SYSTEMTIME _ ) As Long Public Function ConvertUtcTimeToLocalTime(ByVal UtcDateTime As Date) As Date ' L'heure fournie est exprimée en UTC et au format du système ' L'heure renvoyée sera exprimée en heure locale, et toujours au format du système ' Accepte n'importe quel format de date du moment que ce format est le format de l'OS Dim tzi As TIME_ZONE_INFORMATION Dim UtcTime As SYSTEMTIME Dim LocalTime As SYSTEMTIME Call GetTimeZoneInformation(tzi) With UtcTime .wDay = Day(UtcDateTime) .wMonth = Month(UtcDateTime) .wYear = Year(UtcDateTime) .wHour = Hour(UtcDateTime) .wMinute = Minute(UtcDateTime) .wSecond = Second(UtcDateTime) End With Call SystemTimeToTzSpecificLocalTime(tzi, UtcTime, LocalTime) With LocalTime ConvertUtcTimeToLocalTime = CDate(DateSerial(.wYear, .wMonth, .wDay) & _ " " & _ TimeSerial(.wHour, .wMinute, .wSecond)) End With End Function Sub Bouton3() Dim UtcDateTime As Date Dim TempsLocal As Date UtcDateTime = Range("A8").Value 'date UTC' TempsLocal = ConvertUtcTimeToLocalTime(UtcDateTime) 'Date locale' End Sub
Public Enum eDateFormat DATE_LONGDATE = &H2 ' mercredi 24 février 2010 DATE_SHORTDATE = &H1 ' 24/02/2010 End Enum