SBerthold said:
(except in SQL Strings format to use US or ISO 8601 format)
Certainly, dates can be a source of problems for many programmers. Once you understand a few simple rules, you really shouldn't have that many problems.
I will not pretend to know everything about different types of databases, but I do know that this statement is misleading as it pertains to Microsoft SQL Server. In fact, my purpose for bringing this up is to clear away any misunderstandings.
The root of the problem is the way various countries specify their dates. In the US, it is month/day/year. In the UK it is day/month/year. There is a lot of mistaken people out there, even many MVP's. For example, take a look at this:
Louis Davidson, a noted MVP for SQL Server says...
There are some ISO date time formates that are the best practice. Generally speaking, the formats are:
YYYYMMDD or YYYY-MM-DD
Luckily, Umachandar Jayachandran, a Microsoft employee working on the development of SQL Server later responded with:
There are only two unambiguous formats (one for date and another for datetime). Both are ISO formats.
YYYYMMDD - ISO unseparated date format
YYYY-MM-DDThh:mm:ss.nnn - ISO 8601 timestamp format
While Umachander has cleared the air, I think he has missed an unambiguous format. Specifically,
YYYYMMDD hh:mm:ss.nnn.
To prove my point, open a Query Analyzer window and run this code.
Code:
Set DateFormat [!]MDY[/!]
[green]-- Returns [!]1[/!] and 1[/green]
Select Month('2007-01-02'), Month('20070102')
Set DateFormat [!]DMY[/!]
[green]-- Returns [!]2[/!] and 1[/green]
Select Month('2007-01-02'), Month('20070102')
The next part of the puzzle...
By now, you're probably thinking, "I've been using a date format of mm/dd/yyyy for several years now, and I've never had a problem". For me, this was also true until I sold my software in Canada, where they use dd/mm/yyyy. It took me a long time to figure out why, but I finally did.
SQL Server is a client/server technology. In most cases, the application is running on a different computer than the database. As such, it is possible for the regional settings to be different between the machines. In SQL Server, each login has a language. You can see what the language setting is by running the following command:
[tt][blue]Select @@language[/blue][/tt]
To see what your dateformat is:
[tt][blue]Select DateFormat from syslanguages Where Name = @@Language[/blue][/tt]
The important part to realize is that each login
can have a different language setting. Therefore, it's possible that a query built in VB would work for one user but not another. By using the ISO Unseparated Date Format (yyyymmdd), this will not happen.
The last piece of the puzzle...
Many of us know that we should be using the ADO Command object when interacting with the database. Equally important is to use the ADO Parameter object in conjunction with the command object. When you use a parameter object and specify a data parameter, all of the silly date conversions happen locally. This is important. If your local regional settings are dd/mm/yyyy but your SQL Server login is configured for mm/dd/yyyy, it will not matter. When setting the value of the parameter object, a conversion from string to date occurs locally, using your computers regional settings. Then, by the time the sql command is actually executed, you are dealing with a proper date, so there is no ambiguity. This is a benefit of using the command object.
Summary....
When dealing with dates and SQL Server, it is best to use a command object. If that is not practical, then using the ISO Unseparated Date Format (yyyymmdd) is the ONLY safe way to handle dates.
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom