How do I delimit/format dates for database entry?
So many people have problems trying to insert date values into a database. Usually, this is because they don't delimit them properly, or they don't format them properly. For SQL Server, a date should be delimited and formatted as:
If you need to include a timestamp, then you need one of the following formats, depending on whether or not you need milliseconds:
(If you are dealing with today's date, or a DATETIME value that is calculated relative to the current date/time, please consider using GETDATE() or CURRENT_TIMESTAMP within the database. There is no sense introducing complication by using a string to pass in a value that the database is perfectly capable of generating internally without any chance for ambiguity.)
For Access, a date should always be delimited and formatted as:
Anything else might work in limited testing, but can often lead to unexpected results or errors.
As for the delimiters, SQL Server treats DATETIME values as strings, so you always want to surround dates with apostrophe ('). In Access, the newer versions support both apostrophe (') and hash/pound (#) as a delimiter for dates.
As for the format, the problem is that you don't want to rely on ASP or the server's regional settings to decide what date the user meant. You can't think for your user, but your form can certainly say "enter dates in this format." Preferably, your client-side code should request YYYYMMDD or YYYY-MM-DD and validate for that, then the server-side doesn't have to do much work at all. However, in most cases, users are accustomed to entering dates in a certain format, and we certainly don't expect users to change. One workaround would be to use a calendar control of some kind on the client (like Expedia and other sites do), then the user is never entering a date by hand, and you can control the format throughout the process.
Let's demonstrate some of the problems you might come across with bad date formatting. Let's create this table in SQL Server:
In SQL Server, you can't delimit dates with #.
Here is what happens:
If you don't use a delimiter at all, you will get unexpected results.
Since you didn't use a delimiter around the date, this actually evaluates a numeric expression (7 divided by 20 divided by 2004), and when implicitly converted to a SMALLDATETIME value, the column is populated with:
Next, let's try using D/M/Y with the proper delimiters on an M/D/Y system.
This yields the following error, since there is no 20th month:
This would yield the same error if you used M/D/Y format, and your language option was set to a locale that observes DMY, e.g. FRENCH. Observe the following problems:
As you can see, the only safe format for all regions in SQL Server is YYYYMMDD. The following will always work fine, regardless of language or dateformat settings:
Now, let's try out these samples in Access.
These both work fine. Luckily, MS Access (at least on a US English locale), like other Office products, can take an illegal date (like 20/07/2004) and transpose the month and day, if that yields a valid date (07/20/2004). I don't think it's safe to let the software decide for you that an illegal date should suddenly become legal, and insert an unexpected value silently. I think the code should come back to the user and tell them that they entered the date incorrectly.
Of course, since the date was not delimited, Access interprets this as a numeric expression (7 divided by 20 divided by 2004). Like SQL Server, Access can take an integer and implicitly convert it to a date/time value. In this case, the value that gets inserted is:
Clearly, not what was intended.
The newer versions of Access (e.g. Access 2003) will accept this format. Earlier versions would produce a syntax error or data type mismatch error if you didn't use # delimiters for dates.
The YYYYMMDD format, as mentioned earlier, is not accepted by Access. Here are the errors that come from the above statements:
Finally, the only formats you should use, since they avoid all of the problems mentioned above:
You can use ' as a delimiter in Access as long as you know your code will always run against a newer version. If there is a chance you will connect to legacy Access versions, you might be safer continuing to use the # delimiter.
Having said all of that, here is a function you can use to properly format a submitted date so that you never have to worry about errors or transposed month and day (assuming the user actually enters a valid date):
For more information, see Article #2260, Article #2313, and Article #2460.
For more information on the SQL Server side of things, see Tibor's article: the ultimate guide to the datetime datat....
Related ArticlesCan I get millisecond accuracy in ASP?
Can I make VBScript format dates for me?
Could I get a little help with dates?
Given a date, how do I find the beginning and end of that week?
Given two dates, how do I determine an age?
How do I calculate dates, such as the first day of the month?
How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a timespan, in seconds, to HH:MM:SS?
How do I convert local time to UTC (GMT) time?
How do I count the number of business days between two dates?
How do I determine the number of seconds since 1/1/1970?
How do I display time in military format?
How do I implement a calendar / datepicker in ASP?
How do I select time only from a DATETIME column?
Why do I have problems inserting NOW() into a database?