Problem with inserting date value into MSSQL

Igal

I'm trying to insert a date value into MSSQL, the type of the sql filed
is: "smalldatetime"
and i'm trying to insert a text Variable that looks like this:
"19/02/2006".

.... SET update_date='" & Update_Date & "' ...

i get the message:

The conversion of char data type to smalldatetime data type resulted in
an out-of-range smalldatetime value

Nov 19 '06 #1

Bob Barrows [MVP]

Igal wrote:

I'm trying to insert a date value into MSSQL, the type of the sql
filed is: "smalldatetime"
and i'm trying to insert a text Variable that looks like this:
"19/02/2006".

... SET update_date='" & Update_Date & "' ...

i get the message:

The conversion of char data type to smalldatetime data type resulted
in an out-of-range smalldatetime value

Always use a universally standard format for supplying dates to a database.
The ISO standard, YYYYMMDD works very well. Here are a couple articles about
handling dates:
http://www.aspfaq.com/show.asp?id=2313
http://www.aspfaq.com/show.asp?id=2040
http://www.aspfaq.com/show.asp?id=2260
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

Personally, I prefer using stored procedures.
http://groups.google.com/group/micro...9dc1701?hl=en&

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Nov 19 '06 #2

Igal

in the end i used a functions that converts the date to SQL format
(YYYY/MM/DD)
and that worked fine.
but for this application nothing much u can do, it's very old code, and
i'm trying some how to polish, i'm php programmer, not asp actually :)
will prolly write a new one in time for the whole system.

this article is very interesting and helpful. thanks for the
information.

http://mvp.unixwiz.net/techtips/sql-injection.html

Nov 20 '06 #3