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:
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).Code: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
Here are some ways to keep to Date values as much as possible:
Getting input from the userHow to safely convert a String to a Date
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:
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.Code: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
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:
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.Code: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
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.
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:
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!Code:'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))
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!
Reply With Quote