Classic VB - Why are my dates not working properly?

[si_the_geek]

Many people have problems when working with dates/times in their programs, and usually these come down to one or more of these issues:

• understanding what is a date and what isn't, as it is not immediately clear.
  
  
• not knowing how/when data types get automatically converted (known as datatype coercion), as VB usually deals with it well without you realising - but it can have big problems with dates.
  
  
• not knowing how to safely convert other data types to/from the Date data type.

These issues (and more) are explained in the following sections:
   What is a date, and what isn't?
   How are dates are actually stored?
   What causes problems?
   How can I avoid problems? (includes an example of safe String to Date conversion)
   Isn't this too much hassle? Why don't I just use Strings all the time instead?


What is a date, and what isn't? (How to specify dates in your code)
In normal life, something like 09/05/2007 (or 5/9/2007, or 5-9-2007, or whichever style you use) is a date.. but awkwardly with computers, these aren't dates - they are calculations that return completely different values (eg: 09/05/2007 is equivalent to (9/5)/2007 = 1.8/2007 = 0.000897 !), but annoyingly will be interpreted as dates (just very wrong ones!).

Similarly, "09/05/2007" (or "5/9/2007", etc) is not a date either, it is just a String (text). You might think that this is fine, as VB accepts it and seems to work with it properly.. but all it takes is a minor change to Regional Settings (the date/number format settings on your computer) for this to give the wrong value - as an automatic data type conversion occurs according to the Regional Settings of the computer your code/executable is running on, not according to the settings when you wrote the code.

Here are two methods that you should not use:

Dim MyDate as Date
  MyDate = 9/5/2007    'Will be converted to a value equivalent to 1:17 AM (with no date)

  MyDate = "9/5/2007"  'Will be converted to a date, but it could be May 9th or Sept 5th
                       '(depends on the Regional Settings of the computer this runs on)

If you think the second line is actually OK, write and a program that contains just MsgBox Month("9/5/2007") , and run it (in the IDE or compiled) with different date formats for your Regional Settings (which you can change in Control Panel).


The correct way to specify dates in your code is by using the # character, eg:

Dim MyDate as Date
  MyDate = #9/5/2007#  'always means September 5th 2007

Note that the format needs to be m/d/yyyy (no matter what format you usually use!), but VB will automatically convert from your usual format when you type the final # (on my computer the date format is dd/mm/yyyy, but when I entered #05/09/2007# it was automatically converted to m/d/yyyy format as above).

Another safe method is to use the DateSerial function, which has named parameters (Year,Month,Day), which would be like this:

Dim MyDate as Date
  myDate = DateSerial(2007, 9, 5)  'always means September 5th 2007

How are dates are actually stored?
When you look at the value of a Date variable (in a textbox, MsgBox, Code/Watch window, etc), it is shown to you according to your Regional Settings (so for me on the 5th of September, MsgBox Date shows 05/09/2007), but it is not actually stored like that - it is only shown to you that way so that you can easily understand it.

The way dates/times are actually stored is as numbers with a decimal part (in the same way as a Double variable). The Integer part of the number represents the actual date (the number of days since Dec 30 1899), and the fractional (non-integer) part represents the time within that day.

For example, at 6pm on the 5th of September 2007, the date was actually stored like this: 39330.75 (the 39330 refers to the date, and the .75 refers to the time, as 6pm is 75% of the way through the day).

You can check that this method of storage is the case by using the following code: MsgBox CDbl(Now) , or by reading the (brief!) help for "Date Data Type".


What causes problems?
Problems with dates occur when the data type gets converted, either to a Date, or from a Date.

Conversion takes place whenever you put a string/number value into a Date (as shown the "should not use" example above), or when you put a Date value into a string/number variable or property.


The most common cause of problems we see on the forums is putting a String value into a Date. Whatever way that happens, the value you get cannot be trusted - as it is automatically converted to a Date according to the Regional Settings on that computer. If the String is "9/5/2007" it could be interpreted as May 9th 2007 or Sept 5th 2007, and if the String is "9/5/07" it can be interpreted in many different ways (such as May 9th 2007, Sept 5th 2007, July 5 2009, ...).


The only time that converting String to Date is safe is when there is no amibiguity at all - so the Year part needs to be too bigger than any month or day (so larger than 31), and unless the month is in words (eg: "June") the Day part also needs bigger than any month (so larger than 12). Note that using words for the month is not advisable if any of your users might use a different language, as the actual word which is used or can be interpreted (your language or theirs) depends on what function/method/etc you are using, and may not be consistent.

In theory the CDate function (or implied conversion if you haven't specified CDate) should try converting any less obvious values to dates based on your Regional Settings, however several people have found that it tends to try the mm/dd/yyyy format first, and only if that doesn't produce a valid date will it use Regional Settings.


Here are some examples of where a String gets converted to a Date:

Getting input from the user
To get the value of a TextBox you use the .Text property (even if you don't specify anything), which is a String. The InputBox function also returns a String.

That means these methods of getting a date value from the user are not safe:

  ** these are unsafe **
Dim MyDate as Date
  myDate = Text1.Text
  myDate = Text1
  myDate = InputBox("Enter a date")

These methods also have another big problem, which is that if the value entered cannot be converted to date (such as the typo "9/55/2007") you will get a "Type Mismatch" error (as the the value cannot be converted to the target Data Type).


Calling date related functions
Several functions that are date related accept a Date (or even "Any valid expression.") as a parameter, and if you put anything other than a Date into them (such as a String), it will need to be converted.

These functions include CDate, Format, DateAdd, DateDiff, and many others.

  ** these are unsafe **
Dim MyDate as Date, MyString as String
  MyString = Format("9/5/2007", "m")   'This may be 5 or 9, depending on Regional Settings
  MyDate = DateAdd("d", 1, "9/5/2007") 'This may be Sept 6th, or May 10th
  MyDate = CDate(Text1.Text)

Putting the return value of a function (especially Format or Date$) into a Date
Each function returns a value, and like any other value they have a Data Type. If the return type is not a Date, you are likely to get problems.

If you look at the help for the Format function, you will see that it returns a Variant (String), which for all intents and purposes is a String - but even worse than that, it is a String which is a particular order you specified, and so it is even more likely to be converted wrongly.

The Date$ function (but not the Date function) like other functions with a $ variation, also returns a string, but always in the format of mm-dd-yyyy.

  ** these are unsafe **
Dim MyDate as Date
  MyDate = Format(#9/5/2007#, "dd/mm/yyyy") 'returns the String "05/09/2007" 
  MyDate = Date$                            'returns a String, in the format of mm-dd-yyyy
  MyDate = CDate(Format(#9/5/2007#, "mm/dd/yyyy"))   'CDate has the same problems as an implied conversion!

Each of these examples returns a String formatted in a particular way - apart from a few specific situations, you are very likely to get the wrong Date value.

[si_the_geek]

How can I avoid problems?
There are several different things that you should do, but the general idea is a simple one - keep any dates you are working with as dates, and do not convert them from/to other Data Types unless you really need to.

An important thing to remember is that Dates are not actually stored in any particular display format (as explained above), they are stored numerically so that the format does not need to be interpreted/converted. A String is never a date - it is just text that represents a date, and quite often will be in the 'wrong' format for VB to interpret it correctly.

If you do need to convert to a String, use a separate variable so that you don't need to convert it back to a Date again.

When you are displaying a date/time to the user (such as in a Label control or MsgBox), it will be converted to a string, so at that point you should consider using the Format function to display it as you want (if you don't use the Format function, it will be automatically formatted using the Regional Settings of the computer it runs on), eg:

Dim MyTime as Date
  MyTime = #13:00:00#

  Label1.Caption = Format(MyTime, "h:nn AM/PM")  'Will be shown as "1:00 PM"
  Label1.Caption = MyTime                        'Will be shown using Regional Settings

As explained in the previous section, you are likely to have issues if you try to get the value back out of the control (even though this example is for time rather than date, there is still the chance of error).


Here are some ways to keep to Date values as much as possible:

Getting input from the user
As explained above, it is not safe to get a date from a String value (as you would from a TextBox or InputBox etc), so what can you do?

There are special controls which are designed to input a date and/or time (such as the DateTimePicker), or you could use multiple controls (such as three ComboBoxes). For explanations and examples of these options, see the FAQ article What controls can I use to input a date/time?

By using a specific date/time based control, you will actually input a Date value (not just a String that in some way represents a date). For other controls there are methods you can use to convert correctly, as shown in the other article.


Calling date related functions
When you call a date related function (such as Format or DateAdd), supply a Date value as a parameter, eg:

Dim MyDate as Date, MyString as String
  MyString = Format(#9/5/2007#, "m")   'This will return "9"
  MyDate = DateAdd("d", 1, #9/5/2007#) 'This will be Sept 6th 2007

If you have a String which contains a date (or rather, a formatted representation of a date!), you should convert it to a Date first, using a method like the one shown later.


Putting the return value of a function (especially Format or Date$) into a Date
When you call functions like Format and Date$, you are getting a String value back - so it should be stored in a String.

The first thing you should ask yourself is "why am I doing this?". Far too often we see people trying to use a formatted String when actually the Date data type is what is wanted by the function/property/etc they are using - so there is no need to use functions like these at all (simply pass the Date!).

If you do actually need to get a String, calling these functions is fine - but remember to store it into a String variable, or put the value directly into a String. eg:

Dim MyDate as Date, MyString as String
  MyDate = #9/5/2007# 
  MyString = Format(MyDate, "dd/mm/yyyy") 'returns the String "05/09/2007" 
  MyString = Date$                        'returns a String, in the format of mm-dd-yyyy

  MsgBox MyString

Make sure you remember that converting a String back to a Date is not a good idea, so try to only use these functions as a one-way process, and continue to use the Date variable(s) within your code for any date related work.


Database work
The methods for using dates when working with databases isn't much different, but as there are various methods for working with databases, you can find information and examples in this article from our Database Development FAQs.

How to safely convert a String to a Date
As has already been explained, using CDate (or not using anything, thus coercion) is not safe as it makes assumptions based on things that may not be correct; even worse it will happily convert what seems like invalid text to a date (for example, CDate("2007") returns June 29th 1905!).

So what can you do? Well to be safe you need to write your own code to parse the String and convert it to a Date - and you need to know what format the String value is in (if you don't, there isn't anything you can safely do!).

If you know the format of the String, you can write your own code to interpret it. Here is example code based on leinad31's post:

'This example is assumes that the text was entered in the format of mm/dd/yyyy
Dim MyDate as Date
Dim TempArray() as String

    'Separate the items by the delimiter (in this example, "/") 
  TempArray() = Split(Text1.Text, "/")

    'Note: you should check here that the values are valid 
    '(such as the month is a whole number, between 1 and 12)

    'Place each item in the relevant part of DateSerial to build the date
  MyDate = DateSerial(TempArray(2), TempArray(0), TempArray(1))

The comment in the middle is rather important, as DateSerial allows you to specify month and day values that aren't strictly correct, and will convert them to valid dates. For example: you can enter a day of 0, and the date returned will actually be the last day of the previous month!

Unfortunately you will need to write separate code for each format that you want to support, and will need to make sure you use the right version for the situation.

Note that if the String format you are using contains a 'written' version of a month (such as "March" or "Mar", instead of "03" or "3") then the code may need to change if your program is used on a version of Windows which is set to use a different language.


Isn't this too much hassle? Why don't I just use Strings all the time instead?
Some people prefer to just use String variables (in a particular format) to store their date values.

This does have the advantage that you do not need to worry about conversion problems, but it also means that you can no longer use many date related functions (such as DateAdd or DateDiff), and instead create and test your own versions of those functions if you need them.

Assuming you use more than one or two of those functions, the amount of extra work involved is far higher than if you had simply used Date variables to start with.

The choice is yours, but personally I wouldn't use Strings (except for output) because using Dates is fairly easy as long as you are aware of Data Types and conversions. Note that the awkward issue of converting a String to a Date exists in both methods, as for date values in Strings you would also need to convert from one String format to another!