Initialise Date type Variable in VB6

[Lennie]

Hi there,

I am having 2 problems.
Problem #1.
Initialising Date Type variable and have no success in it. I am constantly getting this error message "Data MisMatch".

Problem #2.
I am also facing another problem with check Date Range.

Here is the sample VB6 script.


Option Explicit

dim dteStartDate as date
dim dteEndDate as date

'Intialise Date Variable -----
Private Function FSalesDate()


dteStartDate =""
dteEndDate = ""

End Function

'-------------------------------
'Date Range check. -----
Private Function FDateRangeCheck
if (dteEndDate < dteStartDate) then
xxxxxxxxxx
endif
End Function

[Ron Weller]

The date data type is a numeric type not a string and holds both the date and time.
All numeric data types initialize to zero and so a zero date is equivalent to 12/30/1899 12:00:00 AM

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1/1/100 to 12/31/9999 and times from 0:00:00 to 23:59:59

dteStartDate = Now() 'sets the variable to the current date and time.
dteStartDate = Date() 'sets the variable to the current date, time would be 12:00:00 AM.
dteStartDate = #12/31/2008 1:30:00 PM# 'sets a specific date and time

Also if the time part of your date-time value is 12:00:00 AM then the time won't display, unless the date happens to be 12/30/1899 then the date does not show but the time does.
So for example if you set your date to 12/30/1899 12:00:00 AM it will only display the time without the date. i.e. 12:00:00:AM

If you need to display both, or always the date, or always the time then you will need to use the Format() function.
Format(dteStartDate, "mm/dd/yyyy") 'Always show just the Date
Format(dteStartDate, "hh:nn:ss AMPM") 'Always show just the Time
Format(dteStartDate, "mm/dd/yyyy hh:nn:ss AMPM") 'Always show both Date and Time

[Hack]

I actually use string variables for dates and then convert them with the CDate function is needed. I've found that to be far less prone to headaches.

[Lennie]

Hey Hack,
Thank for your suggestion. I tried it out and it's working. And Ron, thanks to you too for the sample scripts. Both of you are just awesome sharing your experiences and knowledge.

Thanks.

Have a good day

[Hack]

Glad to help!

[CptNeutral]

Just noticed, in some cases, it also depends on the system's time format, if you use

testDate = #1/2/2008#

That could be taken as 1st Feb 2008 or the 2nd Jan 2008.
So what you need to do is this:

testDate = DateSerial(2008, 2, 1)

which would be the date for 1st Feb 2008.



Also, there's a similar function for time

TimeSerial Hour, Minute, Second

[Lennie]

Hi Hank, Ron,
How to initialise the variable date after using it ?

Is this right?
DteStartDate = ""

Cheers,

[Hack]

Hi Hank, Ron,
How to initialise the variable date after using it ?

Is this right?
DteStartDate = ""

Cheers,

Only is DteStartDate is a string. If DteStartDate is a Date variable, that would result in a Type Mismatch.

[Ron Weller]

If your date variable is a string then yes:
dteStartDate = ""
is correct but you will get a type mismatch error if you try to use CDate() on an empty string. In which case you could use:
dteStartDate = "1/1/100"
or
dteStartDate = "12/30/1899"


If your date variable is a type date then it must be set to a date.
the smallest date would be #1/1/100#
dteStartDate = #1/1/100#

A date variable that has never had a date stored in it defaults to 12/30/1899 12:00:00 AM which will only display as 12:00:00 AM
dteStartDate = #12:00:00 AM#
Remember that for a date variable #12/30/1899 12:00:00 AM# has a numeric value of zero

[JSpyder]

In VB6 the date format should be used as the following...

sRequestedDate = Format$(sRequestedDate, goIMDateFormat.sISODateFormat)

'If you want to allow for localized dates

'Defined as...

Private msRequestedDate As String

'When reading a result set from the data base you would want to place it in 
'a string variable but format the data when reading so it is always in 
'the correct and expected format.
'Example...
'Where sRequestedDate is defined in my class
'And LT is Localized Time Zone
         'Completed Date Time LTZ
         sTmpString = Space$(gDATE_L)
         Call db_Check(SQLGetData(gHstmt, 10, SQL_CHAR, ByVal sTmpString, gDATE_L, glSize), msMODULE & gsDEFAULT_DELIM_MINOR & sPROCEDURE)

         If glSize = 0 Or glSize = -1 Then 'have to check both because nulls are allowed in the db
                .sRequestedDate = vbNullString
         Else
               .sRequestedDate = Format(Left(ut_RTrim(sTmpString, glSize), 16), goIMDateFormat.sShortDateTimeFormat)
         End If

'When in your form setting properties on the date picker you might 
'want min & max dates.

'Example...

dateRequestFrom.MinDate = CDate(Format$(gsREQ_MINDATE, goIMDateFormat.sISODateFormat))
dateRequestFrom.MaxDate = mdCurrentDate
dateRequestTo.MinDate = CDate(Format$(gsREQ_MINDATE, goIMDateFormat.sISODateFormat))
dateRequestTo.MaxDate = mdCurrentDate

'Listed below are all the functionality you might ever need when 
'formatting dates in VB6

Option Explicit

Private Const msMODULE As String = "CDateFormat"

Private mlDebugID As Long
Private mbShowCentury As Boolean
Private msShortDateFormat As String
Private msShortDateTimeFormat As String
Private msShortDateTimeSecondFormat As String
Private msMilitaryTimeFormat As String
Private msMilitaryTimeSecondFormat As String
Private msISODateFormat As String
Private msISODateTimeSecondFormat As String
Private msDateDelimiter As String      
Private msDateFormatddmmmyyyyhhss As String 
Private msDecimalSymbolNumber As String
Private msDecimalSymbolCurrency As String
Private msCurrencySymbol As String
Private msCurrencyFormatString2Dec As String
Private msCurrencyFormatString3Dec As String

Private Function GetUserLocaleInfo(ByVal vlLocaleId As Long, ByVal vlLCType As Long) As String

Const sPROCEDURE As String = "GetUserLocaleInfo"

Dim sReturn As String
Dim l As Long

1       l = GetLocaleInfo(vlLocaleId, vlLCType, sReturn, Len(sReturn))
        'if successful..
2       If l Then
            'pad the buffer with spaces
3           sReturn = Space$(l)
            'and call again passing the buffer
4           l = GetLocaleInfo(vlLocaleId, vlLCType, sReturn, Len(sReturn))
            'if successful (l > 0)
5           If l Then
                'l holds the size of the string
                'including the terminating null
6               GetUserLocaleInfo = Left$(sReturn, l - 1)
7           End If

8       End If

End Function

Private Function SetDateFormat() As Boolean

Dim sShortDateFormat As String
Dim sDateDelimiter As String

1       sShortDateFormat = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SSHORTDATE)
2       sDateDelimiter = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SDATE)
        'If ValidateDateFormat Then
3       If InStr(sShortDateFormat, "yyyy") > 0 Then
4           mbShowCentury = True
5       End If

6       msShortDateFormat = sShortDateFormat
7       msShortDateTimeFormat = sShortDateFormat & " hh:mm"
        msShortDateTimeSecondFormat = sShortDateFormat & " hh:mm:ss"
8       msMilitaryTimeFormat = "hh\:mm"
9       msMilitaryTimeSecondFormat = "hh\:mm\:ss"
10      msISODateFormat = "yyyy-mm-dd"
11      msISODateTimeSecondFormat = msISODateFormat & " hh:mm:ss"
12      SetDateFormat = True
13      msDateDelimiter = sDateDelimiter
14      msDateFormatddmmmyyyyhhss = "dd-mmm-yyyy Hh:Nn"
15      msDecimalSymbolNumber = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SDECIMAL)
16      msDecimalSymbolCurrency = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SMONDECIMALSEP)
17      msCurrencySymbol = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SCURRENCY)
18      msCurrencyFormatString2Dec = msCurrencySymbol & " ###,##0.00#"
19      msCurrencyFormatString3Dec = msCurrencySymbol & " ###,##0.000#"

End Function

Public Function ValidateDateFormat(saErrorMessage() As String, ByRef sErrorMessage As String) As Boolean

Const sPROCEDURE As String = "ValidateDateFormat"

Const sValidDecimalDigit As String = "., *"  
Const iERR_INVALID_REGIONAL_SETTINGS_HEADER As Integer = 0
Const iERR_INVALID_REGIONAL_SETTINGS As Integer = 1
Const iERR_INVALID_TIME_SEPARATOR As Integer = 2
Const iERR_INVALID_DECIMAL_SYMBOL As Integer = 3

Dim bInvalidFormat As Boolean
Dim sShortDateFormat As String
Dim sDateDelimiter As String
Dim avSplit As Variant
Dim dDateValidate As Date
Dim sDateValidate As String
Dim sDecimalSeparatorNum As String
Dim sDigitGroupingNum As String
Dim sDecimalSeparatorCur As String
Dim sDigitGroupingCur As String
Dim iErrCount As Integer

1       sShortDateFormat = LCase(GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SSHORTDATE))
2       sDateDelimiter = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SDATE)
3       sDecimalSeparatorNum = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SDECIMAL)
4       sDigitGroupingNum = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_STHOUSAND)
5       sDecimalSeparatorCur = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SMONDECIMALSEP)
6       sDigitGroupingCur = GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_SMONTHOUSANDSEP)
7       bInvalidFormat = False

8       If GetUserLocaleInfo(GetUserDefaultLCID, modWinAPI.LOCALE_STIME) <> ":" Then
9           sErrorMessage = sErrorMessage & vbCrLf & saErrorMessage(iERR_INVALID_TIME_SEPARATOR)
10          iErrCount = iErrCount + 1
11      End If

        'Decimal / Digit Separator Issue
12      If InStr(sValidDecimalDigit, sDecimalSeparatorNum) = 0 Or _
            InStr(sValidDecimalDigit, sDigitGroupingNum) = 0 Or _
            InStr(sValidDecimalDigit, sDecimalSeparatorCur) = 0 Or _
            InStr(sValidDecimalDigit, sDigitGroupingCur) = 0 Then
13          sErrorMessage = sErrorMessage & vbCrLf & saErrorMessage(iERR_INVALID_DECIMAL_SYMBOL)
14          iErrCount = iErrCount + 1
15      End If

        'VB must be able to interpret the implicit string datetime as a datetime.
16      If IsDate(CStr(Now())) = False Then

17          If InStr(sErrorMessage, saErrorMessage(iERR_INVALID_TIME_SEPARATOR)) = 0 Then
18              bInvalidFormat = True
19          End If

20      End If

21      If sDateDelimiter <> "." And sDateDelimiter <> "/" And sDateDelimiter <> "-" Then
22          bInvalidFormat = True
23      End If

        'Look for Spaces in short date format
24      If InStr(sShortDateFormat, " ") > 0 Then
25          bInvalidFormat = True
26      End If

        'Look for hybrid date separators
        'If there is only one instance of the date seperator then format is deemed hybrid
        'More than 2 instances indicates a suffix or other unsupported format
27      avSplit = Split(sShortDateFormat, sDateDelimiter)

28      If UBound(avSplit) <> 2 Then
29          bInvalidFormat = True
30      End If

        'Look for Suffix in format (mm.dd.yyyy. or mm.dd.yyyy r.)
31      If Right(sShortDateFormat, 1) <> "m" And _
            Right(sShortDateFormat, 1) <> "d" And _
            Right(sShortDateFormat, 1) <> "y" Then
32          bInvalidFormat = True
33      End If

        'Look for Single or no y as year designator or other unsupported year designator.
34      avSplit = Split(sShortDateFormat, "y")

35      If UBound(avSplit) <> 2 And _
            UBound(avSplit) <> 4 Then
36          bInvalidFormat = True
37      End If

        'Only allow 1 or 2 designator month
38      avSplit = Split(sShortDateFormat, "m")

39      If UBound(avSplit) < 1 Or UBound(avSplit) > 2 Then
40          bInvalidFormat = True
41      End If

        'Only allow 1 or 2 designator day
42      avSplit = Split(sShortDateFormat, "d")

43      If UBound(avSplit) < 1 Or UBound(avSplit) > 2 Then
44          bInvalidFormat = True
45      End If

46      dDateValidate = #12/31/2006#
47      sDateValidate = FormatDateTime(dDateValidate, vbShortDate)

48      If Not IsDate(sDateValidate) Then
49          bInvalidFormat = True
50      End If

        'Finally, insure the year designator is not in the middle.
51      If InStr(Left(sShortDateFormat, 1) & Right(sShortDateFormat, 1), "y") = 0 Then
52          bInvalidFormat = True
53      End If

54      If bInvalidFormat = True Then
55          sErrorMessage = sErrorMessage & vbCrLf & saErrorMessage(iERR_INVALID_REGIONAL_SETTINGS)
56          iErrCount = iErrCount + 1
57      End If

58      If iErrCount = 0 Then
59          ValidateDateFormat = True
60      Else
61          sErrorMessage = saErrorMessage(iERR_INVALID_REGIONAL_SETTINGS_HEADER) & vbCrLf & sErrorMessage
62          ValidateDateFormat = False
63      End If

End Function

Public Function GetCurrencyFormatString(ByVal viNoOfDecimals As Integer) As String
        
       GetCurrencyFormatString = msCurrencySymbol & "###,##0." & String$(viNoOfDecimals, "0") & "#"

End Function