complex date time conversion 1

[impulse24]

Hi,

I have gotten a file from a customer that contains the date time in VMS format, and a separate file that contains the date time format in DD-MMM-YYYY HH:MM:SS.MS

I need to convert the date format(DD-MMM-YYYY HH:MM:SS.MS) in the one file to link to the date format in the other, to create a database, and run code against it.

Example:
2nd file
16-MAR-2004 14:53:30.85
equivalent vms time in first
45861656108500000

More examples:
16-MAR-2004 14:58:43.68 45861659236800000
19-NOV-2002 08:59:11.32 45444131513200000


I searched everywhere for how to achieve in VB, or how the readable date time equates to VMS, and the only thing I could find is:
100 ns granularity, 64 bit tick counter since ?Smithsonian base date? 00:00 17-Nov-1858

So it looks like I would need a formula to count 100ns from Nove-17-1858 to the date/time in the 2nd file. I have no clue where to start, or if vb can count in Nanoseconds. Please help

 

[Hypetia]

Very simple. I never heard of any such format before, but I can write a conversion function using the info and example you provided.
___
[tt]
Private Sub Form_Load()
MsgBox VMStoVBDate("45861656108500000")
MsgBox VMStoVBDate("45861659236800000")
MsgBox VMStoVBDate("45444131513200000")
End Sub
Function VMStoVBDate(VMS As Double) As Date
VMStoVBDate = #11/17/1858# + VMS / 864000000000#
'864000000000 = 10000000 * 60 * 60 * 24 = number of 100 nanoseconds / day
End Function[/tt]

 

[Som76]

Hi
The above reply will help u to get date from the VMS value.
If you need in the other way ie VMS value from date the try this:

Dim dteDate1 As Date
Dim dteDate2 As Date

dteDate2 = "16-MAR-2004 14:53:30"
dteDate1 = "17-Nov-1858 00:00"

Debug.Print DateDiff("s", dteDate1, dteDate2)

This will return u a value in seconds, and from this very easily u can get the value in nano seconds.
Only concern here is it will work for
14:53:30
and not for
14:53:30.85

vb will not recognise the later .85, vb will throw an error.



Som

 

[HarleyQuinn]

Hi,

I have to say I like Hypetia's solution (as been the case a fair few times from memory).

I take it you would be able to use the date in the format provided in that post as the value you use in the database???

Harleyquinn

---------------------------------
For tsunami relief donations

 

[impulse24]

Hypetia's solution is great and solves part of the problem.

Som76 was close but since it doesnt go to the nanosecond I cant use it.

I still need the piece that goes from the normal date time format DD-MMM-YYYY HH:MM:SS.MS to the vms date time

examples:


give date 16-MAR-2004 14:58:43.68
get vmsdate 45861659236800000

give date 19-NOV-2002 08:59:11.32
get vmsdate 45444131513200000

I tried tweaking the function from hypetia but kept getting stuck. Please help. Thanks

 

[Hypetia]

Note that the VB's Date data type does not store date values down to millisecond resolution. You can only preserve seconds, milliseconds are not stored in Date data type.

If you use dates without fractions of seconds than you can use the following VBToVMSDate function which is simply the inverse of the VMStoVBDate function in my first post.
___
[tt]
Private Sub Form_Load()
MsgBox VMStoVBDate("45861656108500000")
MsgBox VMStoVBDate("45861659236800000")
MsgBox VMStoVBDate("45444131513200000")

MsgBox VBToVMSDate("16-MAR-2004 14:58:43") '45861659230000000
MsgBox VBToVMSDate("19-NOV-2002 08:59:11") '45444131510000000
End Sub
Function VMStoVBDate(VMS As Double) As Date
VMStoVBDate = #11/17/1858# + VMS / 864000000000#
End Function
Function VBToVMSDate(VBDate As Date) As Variant
VBToVMSDate = CDec((VBDate - #11/17/1858#) * 864000000000#)
End Function[/tt]
___

On the other hand, if you insist to preserve your date to fractions of seconds, than you have to use some other data type instead of Date. You also need to write a customized function to incorporate milliseconds in the VMS date.
___
[tt]
Private Sub Form_Load()
'starndard date format
MsgBox VBToVMSDate("16-MAR-2004 14:58:43") '45861659230000000
MsgBox VBToVMSDate("19-NOV-2002 08:59:11") '45444131510000000
'date with fractions of seconds
MsgBox VBToVMSDate("16-MAR-2004 14:58:43.68") '45861659236800000
MsgBox VBToVMSDate("19-NOV-2002 08:59:11.32") '45444131513200000
End Sub
Function VBToVMSDate(vbDate As Variant) As Variant
Dim V() As String
V = Split(vbDate, ".") 'split into date and fractions
VBToVMSDate = CDec((CDate(V(0)) - #11/17/1858#) * 864000000000#)
If UBound(V) Then 'add the fractional part
VBToVMSDate = VBToVMSDate + CDec(Left$(V(1) & "0000000", 7))
End If
End Function[/tt]
___

Hope that helps.

 

[strongm]

Here's an alternative that utilises the fact that UTC time is measured in exactly the same way as VMS time (100 nanosecond intrervals since a base date), the only difference being the base date used.

[blue]Option Explicit

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 Declare Function SystemTimeToFileTime Lib "kernel32" (lpSystemTime As SYSTEMTIME, lpFileTime As Currency) As Long
Private Declare Function FileTimeToSystemTime Lib "kernel32" (lpFileTime As Currency, lpSystemTime As SYSTEMTIME) As Long

' The core information that allows this code to work
' VMS time: 64-bit quantity in 100ns steps since 00:00 17-NOV-1858
' FILETIME (UTC): 64-bit quantity in 100ns steps since 00:00 01-JAN-1601
Private Function VBtoVMS(strDate As String) As Variant
    Dim myTime As Currency
    Dim mySystime As SYSTEMTIME
    Dim Tempdate() As String
    
    Tempdate = Split(strDate, ".")
    If IsDate(Tempdate(0)) Then
        With mySystime
            .wDay = Day(Tempdate(0))
            .wYear = Year(Tempdate(0))
            .wMonth = Month(Tempdate(0))
            .wHour = Hour(Tempdate(0))
            .wMinute = Minute(Tempdate(0))
            .wSecond = Second(Tempdate(0))
            If UBound(Tempdate) = 1 Then
                .wMilliseconds = 10 * CInt(Tempdate(1))
            End If
        End With
        SystemTimeToFileTime mySystime, myTime
    End If
    
    ' 8137756800000 is the (unscaled) difference in base times between UTC (used by Windows)
    ' and VMS time
    VBtoVMS = (myTime - 8137756800000#) * CDec(10000) 'remove currency scaling and force decimal type
    'Beep
End Function

Private Function VMStoVB(decVMSTime As Variant) As String
    Dim myTime As Currency
    Dim mySystime As SYSTEMTIME
    
    ' scale back and add in VMS time offset
    myTime = (decVMSTime / 10000) + 8137756800000#
    ' convert
    FileTimeToSystemTime myTime, mySystime
    With mySystime
        VMStoVB = Format(DateSerial(.wYear, .wMonth, .wDay), "DD-MMM-YYYY ") & Format(TimeSerial(.wHour, .wMinute, .wSecond), "HH:NN:SS") & Format(.wMilliseconds / 1000, ".###")
    End With

End Function

Private Sub Command1_Click()
    Debug.Print VBtoVMS("16-MAR-2004 14:53:30.85") ' 45861656108500000
    Debug.Print VBtoVMS("16-MAR-2004 14:58:43.68") ' 45861659236800000
    Debug.Print VBtoVMS("19-NOV-2002 08:59:11.32") ' 45444131513200000
    
    Debug.Print VMStoVB(getVMSDate("16-MAR-2004 14:53:30.85"))
End Sub[/blue]