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:
'YYYY-MM-DDTHH:MM:SS.xxx'
or
'YYYY-MM-DDTHH:MM:SS' |
(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:
#YYYY-MM-DD#
-- some versions will accept 'YYYY-MM-DD' |
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:
CREATE TABLE Test
(
dt SMALLDATETIME
) |
In SQL Server, you can't delimit dates with #.
sql = "INSERT test(dt) VALUES(#20/07/2004#)"
conn.execute(sql)
sql = "INSERT test(dt) VALUES(#07/20/2004#)"
conn.execute(sql) |
Here is what happens:
Microsoft OLE DB Provider for SQL Server error '80040e14'
The name '#20' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Microsoft OLE DB Provider for SQL Server error '80040e14'
The name '#07' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. |
If you don't use a delimiter at all, you will get unexpected results.
sql = "INSERT test(dt) VALUES(7/20/2004)"
conn.execute(sql) |
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.
sql = "INSERT test(dt) VALUES('20/07/2004')"
conn.execute(sql) |
This yields the following error, since there is no 20th month:
Microsoft OLE DB Provider for SQL Server error '80040e07'
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. |
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:
SET LANGUAGE FRENCH
SELECT CONVERT(SMALLDATETIME, '7/20/2004') -- fails
GO
SELECT CONVERT(SMALLDATETIME, '20/07/2004')
GO
SELECT CONVERT(SMALLDATETIME, '2004-07-20') -- fails
GO
SELECT CONVERT(SMALLDATETIME, '20040720')
GO
SET LANGUAGE ENGLISH
SELECT CONVERT(SMALLDATETIME, '7/20/2004')
GO
SELECT CONVERT(SMALLDATETIME, '20/07/2004') -- fails
GO
SELECT CONVERT(SMALLDATETIME, '2004-07-20')
GO
SELECT CONVERT(SMALLDATETIME, '20040720')
GO |
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:
sql = "INSERT Test(dt) VALUES('20040720')"
conn.execute(sql) |
Now, let's try out these samples in Access.
sql = "INSERT INTO Test(dt) VALUES(#20/07/2004#)"
conn.execute(sql)
sql = "INSERT INTO Test(dt) VALUES(#07/20/2004#)"
conn.execute(sql) |
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.
sql = "INSERT INTO Test(dt) VALUES(7/20/2004)"
conn.execute(sql) |
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.
sql = "INSERT INTO Test(dt) VALUES('20/07/2004')"
conn.execute(sql) |
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.
sql = "INSERT INTO Test(dt) VALUES('20040720')"
conn.execute(sql)
sql = "INSERT INTO Test(dt) VALUES(#20040720#)"
conn.execute(sql) |
The YYYYMMDD format, as mentioned earlier, is not accepted by Access. Here are the errors that come from the above statements:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '#20040720#'. |
Finally, the only formats you should use, since they avoid all of the problems mentioned above:
sql = "INSERT INTO Test(dt) VALUES('2004-07-20')"
conn.execute(sql)
sql = "INSERT INTO Test(dt) VALUES(#2004-07-20#)"
conn.execute(sql) |
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):
<%
Function niceDateSQL(dt)
dts = split(dt, "/")
' assuming m/d/y data entry:
y = dts(2)
m = pad(dts(0), 2)
d = pad(dts(1), 2)
' if people enter d/m/y, swap them:
' d = pad(dts(0), 2)
' m = pad(dts(1), 2)
niceDateSQL = "'" & y & m & d & "'"
End Function
Function niceDateAccess(dt)
dts = split(dt, "/")
' assuming m/d/y data entry:
y = dts(2)
m = pad(dts(0), 2)
d = pad(dts(1), 2)
' if people enter d/m/y, swap them:
' d = pad(dts(0), 2)
' m = pad(dts(1), 2)
niceDateAccess = "#" & y & "-" & m & "-" & d & "#"
End Function
Function pad(n, s)
pad = right(String(s, "0") & n, s)
End Function
' sample usage:
sql = "INSERT INTO Test(dt) VALUES(" & niceDateSQL("7/20/2004") & ")"
Response.Write(sql & "<br>")
sql = "INSERT INTO Test(dt) VALUES(" & niceDateAccess("7/20/2004") & ")"
Response.Write(sql & "<br>")
%> |
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 datatypes.
[ Comment, Edit or Article Submission ]
