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

Did you make sure you are using single quotes around your date variable? i.e.:
"INSERT INTO tablename (datefield) VALUES ('" & datevariable & "')"`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`

Robert

robkdavis@h...

`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`
 




>From: "Nick" 
>Reply-To: "ASP Web HowTo" 
>To: "ASP Web HowTo" 
>Subject: [asp_web_howto] Error inserting date in smalldatetime 
>Date: Sat, 15 Feb 2003 19:07:35 
> 
>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 
>--- 
MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.

> 
>Did you make sure you are using single quotes around your date variable? 
>i.e.:
>"INSERT INTO tablename (datefield) VALUES ('" & datevariable & "')"`°
>º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`


ABSOLUTELY....
Nick


Robert

robkdavis@h...

`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`
 




>From: "Nick" 
>Reply-To: "ASP Web HowTo" 
>To: "ASP Web HowTo" 
>Subject: [asp_web_howto] Error inserting date in smalldatetime 
>Date: Sat, 15 Feb 2003 19:07:35 
> 
>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 
>--- 
MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.

I would Response.Write() your SQL Statement to the page in order to see what you are actually sending to SQL Server, then. No need
to shout!
That's always the first step I take in debugging a SQL Statement, also when you response.write the statement, you may want to paste
it into SQL Query Analyzer to see if it works... i.e.:
MyQuery = "SELECT * FROM tablename"
Response.Write(MyQuery) : Response.End
Hope this helps. :D




>From: "Nick" 
>Reply-To: "ASP Web HowTo" 
>To: "ASP Web HowTo" 
>Subject: [asp_web_howto] Re: Error inserting date in smalldatetime 
>Date: Sun, 16 Feb 2003 09:29:39 
> 
> > 
> >Did you make sure you are using single quotes around your date variable? 
> >i.e.: 
> >"INSERT INTO tablename (datefield) VALUES ('" & datevariable & "')"`° 
> >º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°` 
> 
> 
>ABSOLUTELY.... 
>Nick 
> 
> 
>Robert 
> 
>robkdavis@h... 
> 
>`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°` 
>  
> 
> 
> 
> 
> >From: "Nick" 
> >Reply-To: "ASP Web HowTo" 
> >To: "ASP Web HowTo" 
> >Subject: [asp_web_howto] Error inserting date in smalldatetime 
> >Date: Sat, 15 Feb 2003 19:07:35 
> > 
> >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 
> >--- 
>MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*. 
>--- 
MSN 8 helps ELIMINATE E-MAIL VIRUSES. Get 2 months FREE*.

If you are using an SQL statement that is a string constructed in your ASP
page, then you need to format your string literal date in a format that can
be "intepreted" by SQL Server.

Remember, databases do not store dates in a format like "11/2/2003 9:00 AM",
they store dates/times as numbers. Usually the number of days after some
arbitrary starting date, and the number of seconds since midnight. (Look in
SQL Server Books Online for how a DateTime field is stored).

When you pass a string representation of a date, it needs to be in a format
that SQL Server can intepret.

Use ISO-style formatting to avoid problems:

YYYY/MM/DD HH:MM:SS [AM | PM]

Omitting the time will result in 12:00 AM being stored. Omitting the date
will result in 1 Jan 1900 being stored for the date part.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "Nick" <ektorjr@h...>
To: "ASP Web HowTo" <asp_web_howto@p...>
Sent: Saturday, February 15, 2003 7:07 PM
Subject: [asp_web_howto] 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?