Hello

Suppose many of you have faced this problem before. I try to insert a date form my ASP page in SQL Server 2000 (in a smalldatetime datatype), but this seems impossible.

After receiveing many error messages i found that i had to use the Convert function in my query. Well, I am using:

update games set game_date=CONVERT(smalldatetime, 31/07/2003)

The query works fine, but it inserts the value 01/01/1900 in the database. I also tried others formats of the date (07/31/2003 etc.) but nothing works.

It would be gretaly appreciated if could have a code snippet with an insert statement for SQL Server from ASP contaning dates.

Thanks in advance.

Nick

Can't give you ASP code, but the SQL to update a datetime datatype is:
There are several ways to deal with different date formats due to locale, but the simplest is to always use the ISO standard date format, i.e. yyyy-mm-dd. That way there is no ambiguity. Express the date as a string and all will be fine.

(BTW, the CONVERT function as you wrote it and the variations you tried will always insert a 0 as the date. A date of 0 is Jan 1, 1900. It will always insert a 0 becuase the 31/7/2003 is an expression interpreted as the integer 31 divided by the integer 7 divided by the integer 2003 truncated to an integer. The other variations also end up dividing by 2003 the result of which is always less than 1 which truncates to a 0.)


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com