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:
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).Code: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)
The correct way to specify dates in your code is by using the # character, eg:
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).Code:Dim MyDate as Date MyDate = #9/5/2007# 'always means September 5th 2007
Another safe method is to use the DateSerial function, which has named parameters (Year,Month,Day), which would be like this:
How are dates are actually stored?Code:Dim MyDate as Date myDate = DateSerial(2007, 9, 5) 'always means September 5th 2007
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 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).Code:** these are unsafe ** Dim MyDate as Date myDate = Text1.Text myDate = Text1 myDate = InputBox("Enter a date")
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.
Putting the return value of a function (especially Format or Date$) into a DateCode:** 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)
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.
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.Code:** 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!