Home > Date and Time > How to Convert 10 Digits Numeral to The Date Value?

How to Convert 10 Digits Numeral to The Date Value?

August 29th, 2009 Masino Sinaga Leave a comment Go to comments

I have hundreds of records that need to be imported from one table and database (let’s say that this is the first database) to another table in different database (and the last one is the second database). Unfortunately, one of the columns in the table in first database has the Date value in 10 digits numeral format. For example: 1206980969. Actually, this value equals with March 31, 2008 16:29:29 on UTC. Then, how do I convert that 10 digits numeral to the Data value since the column in the second database is a Date field type? And how do I convert again that date according to my local time?

Okay. Here is the solution that I made from Visual Basic 6. First of all, you should create a new function for this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

Option Explicit
 
Private Declare Function GetTimeZoneInformation Lib "KERNEL32.dll" (lpTimeZoneInformation As TIME_ZONE_INFORMATION) As Long
 
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         As String * 64
   StandardDate         As SYSTEMTIME
   StandardBias         As Long
   DaylightName         As String * 64
   DaylightDate         As SYSTEMTIME
   DaylightBias         As Long
End Type
 
Private Const TIME_ZONE_ID_DAYLIGHT As Long = 2
Private Const Unix1970   As Long = 25569 'CDbl(DateSerial(1970, 1, 1))

Public Function Unix2Date(vUnixDate As Long, ByVal bReturnUTC As Boolean) As Date
   Unix2Date = DateAdd("s", vUnixDate, Unix1970) - IIf(bReturnUTC, 0, GetCurrentTZOffset)
End Function
 
Public Function GetCurrentTZOffset() As Double
   Dim tz               As TIME_ZONE_INFORMATION
   Dim lRet             As Long
   'Quickest way to see if we are in
   'Daylight savings is to check lRet
   lRet = GetTimeZoneInformation(tz)
   'Offset in minutes
   GetCurrentTZOffset = tz.Bias + IIf(lRet = TIME_ZONE_ID_DAYLIGHT, tz.DaylightBias, tz.StandardBias)
   GetCurrentTZOffset = GetCurrentTZOffset / 1440
End Function
 
Private Sub Command1_Click()
   MsgBox "UTC   = " & Unix2Date(1206980969, True) 'March 31, 2008 16:29:29 (UTC)
   MsgBox "Local = " & Unix2Date(1206980969, False) 'March 31, 2008 23:29:29 (UTC + 7)
End Sub

From the code above, the conclusion is: We can use the Unix2Date function which has two parameters in it. The first parameter is the 10 digits numeral value, and the second parameter is the flag whether you want to convert it to UTC or to your local time. If we want to convert it to UTC, then the second parameter value is True, whereas if we want to convert it to the local time (in this case I have UTC + 7), the second parameter value is False.

  • Share/Bookmark
188 views Print This Post Print This Post

Categories: Date and Time Tags: , , , ,
  1. No comments yet.
  1. No trackbacks yet.