When using dates, use ISO format (yyyy-mm-dd) to avoid these type of errors.

Additionally, you should always output your SQL statement to the screen
using:

<%
Response.Write(strSQL)
Response.End
%>

so that you can see what is actually being sent to the database - one of
your variables might have a value other than what you think.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Christian Dygaard" <cdygaard@m...>
Subject: [asp_databases] Out-of-range datetime value.


: I get the following error message:
:
: The conversion of a char data type to a datetime data type resulted in an
: out-of-range datetime value.
: /indkøbsregnskab/index.asp, line 222
:
: My Code looks like this:
: The last line is line 222.
:
: If Request.Form("WhatWasChosen") = "MonthlyConsumptionChosen" Then
:     Dim strMonthName, intMonth, Year, StartDate, EndDate, intEndDay
: Dim CostFood, CostDrinks, CostCleaning, EndSum1, EndSum2, StartDay
: strMonthName = Request.Form("Month")
: intMonth = ChangeMonthNameToInteger(strMonthName)
: Year = Request.Form("Year")
: StartDay = 01
: StartDate = CDate(StartDay & "-" & intMonth & "-" & Year)
: StartDate = CDate(DatePart("yyyy", StartDate) & "-" & DatePart("m",
: StartDate) & "-" & DatePart("d", StartDate))
: intEndDay = CalculateEndDate(strMonthName, Year)
: EndDate = CDate(intEndDay & "-" & intMonth & "-" & Year)
: EndDate = CDate(DatePart("yyyy", EndDate) & "-" & DatePart("m", EndDate)
: & "-" & DatePart("d", EndDate))
: Set objConn = Server.Createobject("ADODB.Connection")
:             strConnString = "Provider=SQLOLEDB;" & _
:                             "Persist Security Info=FALSE;" & _
:                             "User ID=sa;" & _
:                             "Password=pb16950;" & _
:                             "Initial Catalog=Indkøbsregnskab;" & _
:                             "Initial File Name = C:\Programmer\Microsoft
SQL
: Server\MSSQL\" & _
:         "DATA\Indkøbsregnskab_Data.mdf;"
:             objConn.Open strConnString
: strSQL = "SELECT * FROM Consumption_T WHERE Date_DT >= '" & StartDate &
: "'" & _
:          " AND Date_DT <= '" & EndDate & "' ORDER BY Date_DT"
: Set objRS = Server.Createobject("ADODB.Recordset")
:         objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly,
AdCmdText
:
: What's the problem?
:
: Best regards
:
: Christian Dygaard

please try to change ' with #

: strSQL = "SELECT * FROM Consumption_T WHERE Date_DT >= '" & StartDate &
: "'" & _
:          " AND Date_DT <= '" & EndDate & "' ORDER BY Date_DT"

so

: strSQL = "SELECT * FROM Consumption_T WHERE Date_DT >= #" & StartDate &
: "#" & _
:          " AND Date_DT <= #" & EndDate & "# ORDER BY Date_DT"


----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Tuesday, September 03, 2002 2:04 PM
Subject: [asp_databases] Re: Out-of-range datetime value.


> When using dates, use ISO format (yyyy-mm-dd) to avoid these type of
errors.
>
> Additionally, you should always output your SQL statement to the screen
> using:
>
> <%
> Response.Write(strSQL)
> Response.End
> %>
>
> so that you can see what is actually being sent to the database - one of
> your variables might have a value other than what you think.
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Christian Dygaard" <cdygaard@m...>
> Subject: [asp_databases] Out-of-range datetime value.
>
>
> : I get the following error message:
> :
> : The conversion of a char data type to a datetime data type resulted in
an
> : out-of-range datetime value.
> : /indkøbsregnskab/index.asp, line 222
> :
> : My Code looks like this:
> : The last line is line 222.
> :
> : If Request.Form("WhatWasChosen") = "MonthlyConsumptionChosen" Then
> :     Dim strMonthName, intMonth, Year, StartDate, EndDate, intEndDay
> : Dim CostFood, CostDrinks, CostCleaning, EndSum1, EndSum2, StartDay
> : strMonthName = Request.Form("Month")
> : intMonth = ChangeMonthNameToInteger(strMonthName)
> : Year = Request.Form("Year")
> : StartDay = 01
> : StartDate = CDate(StartDay & "-" & intMonth & "-" & Year)
> : StartDate = CDate(DatePart("yyyy", StartDate) & "-" & DatePart("m",
> : StartDate) & "-" & DatePart("d", StartDate))
> : intEndDay = CalculateEndDate(strMonthName, Year)
> : EndDate = CDate(intEndDay & "-" & intMonth & "-" & Year)
> : EndDate = CDate(DatePart("yyyy", EndDate) & "-" & DatePart("m", EndDate)
> : & "-" & DatePart("d", EndDate))
> : Set objConn = Server.Createobject("ADODB.Connection")
> :             strConnString = "Provider=SQLOLEDB;" & _
> :                             "Persist Security Info=FALSE;" & _
> :                             "User ID=sa;" & _
> :                             "Password=pb16950;" & _
> :                             "Initial Catalog=Indkøbsregnskab;" & _
> :                             "Initial File Name = C:\Programmer\Microsoft
> SQL
> : Server\MSSQL\" & _
> :         "DATA\Indkøbsregnskab_Data.mdf;"
> :             objConn.Open strConnString
> : strSQL = "SELECT * FROM Consumption_T WHERE Date_DT >= '" & StartDate &
> : "'" & _
> :          " AND Date_DT <= '" & EndDate & "' ORDER BY Date_DT"
> : Set objRS = Server.Createobject("ADODB.Recordset")
> :         objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly,
> AdCmdText
> :
> : What's the problem?
> :
> : Best regards
> :
> : Christian Dygaard
>
>
>