Hello all.

I try to execute an "insert" statement in a smalldatetime field of SQL 
Server. I always receive the error:

"Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char 
data type to a datetime data type resulted in an out-of-range datetime 
value.
/admin/add_news.asp, line 108"

This happens even if I pass the VBscript "Date()" statement from my ASP 
page, or create a DD/MM/YYYY-type date. Does anybody know what form of 
Date should I pass in my ASP pages so it can be inserted in SQL Server?

Thanks in advance

Nick

SQL is very picky about datatypes, even though you are passing a date, SQL
is not smart enough to realize that you are not passing a character string.
Try to cast (SQL 7 or higher) or convert (all versions) the value within the
SQL statement.

sSQL = "insert table values(convert(smalldatetime," & varDate & "))"

----- Original Message -----
From: "Nikos Drandakis" <ektorjr@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Saturday, February 15, 2003 7:13 PM
Subject: [asp_databases] Error inserting date in smalldatetime


> Hello all.
>
> I try to execute an "insert" statement in a smalldatetime field of SQL
> Server. I always receive the error:
>
> "Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
> [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
> data type to a datetime data type resulted in an out-of-range datetime
> value.
> /admin/add_news.asp, line 108"
>
> This happens even if I pass the VBscript "Date()" statement from my ASP
> page, or create a DD/MM/YYYY-type date. Does anybody know what form of
> Date should I pass in my ASP pages so it can be inserted in SQL Server?
>
> Thanks in advance
>
> Nick
>

Use ISO date format:

YYYY/MM/DD

(or, if you just want to insert the current date, you can use the SQL Server
GetDate() function in your SQL statement/sproc)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Nikos Drandakis" <ektorjr@h...>
Subject: [asp_databases] Error inserting date in smalldatetime


: I try to execute an "insert" statement in a smalldatetime field of SQL
: Server. I always receive the error:
:
: "Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char
: data type to a datetime data type resulted in an out-of-range datetime
: value.
: /admin/add_news.asp, line 108"
:
: This happens even if I pass the VBscript "Date()" statement from my ASP
: page, or create a DD/MM/YYYY-type date. Does anybody know what form of
: Date should I pass in my ASP pages so it can be inserted in SQL Server?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~