The conversion of a char data type to a datetime data type resulted in an out-of-range ...

Hi,
I couldn't understand why I can′t convert the date. I use dd/mm/yyyy, and I put the correct sintax in my code:

CType(tbVencimento.Text,Date)
but it conti nues recording inside of my database like mm/dd/yyyy, why?
and every moment I receive this error message:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Please, help!
Thanks a lot!
Raphael Amador

You should use DateTime.ParseExact to get the format changed.
Dim s As String
s = "25/12/2005"
Dim d As DateTime
d = DateTime.ParseExact(s, "dd/MM/yyyy", Nothing)
Then when it is inserted into the Sql, since it is coming from a DateTime object, the format will be correct and you won't get the out-of-range value error. And it will show as mm/dd/yyyy format as 12/25/2005.
bill
Hi, I tried:

<%@dotnet.itags.org.PageLanguage="VB" %>

<%@dotnet.itags.org.ImportNamespace="System.Data" %>

<%@dotnet.itags.org.ImportNamespace="System.Data.SQLClient" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.1//EN""http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<scriptrunat="server">

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

tbLiquido.Text =CType(tbTotNF.Text,Decimal) -CType(tbDesconto.Text,Decimal)

lblIData.Text =CDate(Today)

EndSub

ProtectedSub ddlClientes_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim SQLAsString ="Select CODCLI, RAZAO, ENDERECO, ESTADO FROM CLIENTES WHERE CODCLI = '" & ddlClientes.SelectedItem.Value &"'"

Dim Conex?oAs SqlConnection =New SqlConnection("Data Source=SERV-IPE2;Initial Catalog=IpeSys;Persist Security Info=True;User ID=ASPNET;Password=IpeSys")

Dim ComandoSQLAsNew SqlCommand(SQL, Conex?o)

Conex?o.Open()

Dim drAs SqlDataReader

dr = ComandoSQL.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

dr.Read()

tbENDERECO.Text = dr("ENDERECO")

tbESTADO.Text = dr("ESTADO")

Conex?o.Close()

EndSub

ProtectedSub Gravar_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim SQLAsString ="Insert into RECEBER(NFNUMERO_receber, EMPRESA_receber, TIPOFAT_receber, EMISSAO_receber, CODCLI_receber, CLIENTE_receber, ENDERECO_receber, ESTADO_receber, TOTALNOTA_receber, VENCIMENTO_receber, BANCO_receber, CCdoBANCO_receber, DESCONTO_receber, LIQUIDO_receber, SALDOREC_receber,IUSER_receber, IDATE_receber) values (" &CType(tbNOTAFISCAL.Text,Decimal) &",'" & ddlEmpresa.SelectedValue &"','" & rblTipoFat.SelectedValue &"','" &CType(tbDataEmissao.Text, DateTime) &"','" & ddlClientes.SelectedItem.Value &"','" & ddlClientes.SelectedItem.Text &"','" & tbENDERECO.Text &"','" & tbESTADO.Text &"','" &CType(tbTotNF.Text,Decimal) &"','" &CType(tbVencimento.Text, DateTime) &"','" & ddlBancoCob.SelectedItem.Text &"','" & ddlBancoCCorrente.SelectedItem.Text &"','" &CType(tbDesconto.Text,Decimal) &"','" &CType(tbLiquido.Text,Decimal) &"','" &CType(tbLiquido.Text,Decimal) &"','" & lblIUSER.Text &"','" &CType(lblIData.Text,Date) &"')"

Dim Conex?oAs SqlConnection =New SqlConnection("Data Source=SERV-IPE2;Initial Catalog=IpeSys;Persist Security Info=True;User ID=ASPNET;Password=IpeSys")

Dim ComandoSqlAsNew SqlCommand(SQL, Conex?o)

Conex?o.Open()

Dim sAsString

s ="25/12/2005"

Dim dAs DateTime

d = DateTime.ParseExact(s,"dd/MM/yyyy",Nothing)

ComandoSql.ExecuteNonQuery()

Conex?o.Close()

Response.Redirect("Lista.aspx")

EndSub

ProtectedSub CancelButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Response.Redirect("Lista.aspx")

EndSub


But I have the same problem with the out-of-range error. Can you explain me more about your code??
Thanks a lot.
Raphael

Hi, I tried:

<%@dotnet.itags.org.PageLanguage="VB" %>

<%@dotnet.itags.org.ImportNamespace="System.Data" %>

<%@dotnet.itags.org.ImportNamespace="System.Data.SQLClient" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.1//EN""http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<scriptrunat="server">

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

tbLiquido.Text =CType(tbTotNF.Text,Decimal) -CType(tbDesconto.Text,Decimal)

lblIData.Text =CDate(Today)

EndSub

ProtectedSub ddlClientes_SelectedIndexChanged(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim SQLAsString ="Select CODCLI, RAZAO, ENDERECO, ESTADO FROM CLIENTES WHERE CODCLI = '" & ddlClientes.SelectedItem.Value &"'"

Dim Conex?oAs SqlConnection =New SqlConnection("Data Source=SERV-IPE2;Initial Catalog=IpeSys;Persist Security Info=True;User ID=ASPNET;Password=IpeSys")

Dim ComandoSQLAsNew SqlCommand(SQL, Conex?o)

Conex?o.Open()

Dim drAs SqlDataReader

dr = ComandoSQL.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

dr.Read()

tbENDERECO.Text = dr("ENDERECO")

tbESTADO.Text = dr("ESTADO")

Conex?o.Close()

EndSub

ProtectedSub Gravar_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Dim SQLAsString ="Insert into RECEBER(NFNUMERO_receber, EMPRESA_receber, TIPOFAT_receber, EMISSAO_receber, CODCLI_receber, CLIENTE_receber, ENDERECO_receber, ESTADO_receber, TOTALNOTA_receber, VENCIMENTO_receber, BANCO_receber, CCdoBANCO_receber, DESCONTO_receber, LIQUIDO_receber, SALDOREC_receber,IUSER_receber, IDATE_receber) values (" &CType(tbNOTAFISCAL.Text,Decimal) &",'" & ddlEmpresa.SelectedValue &"','" & rblTipoFat.SelectedValue &"','" &CType(tbDataEmissao.Text, DateTime) &"','" & ddlClientes.SelectedItem.Value &"','" & ddlClientes.SelectedItem.Text &"','" & tbENDERECO.Text &"','" & tbESTADO.Text &"','" &CType(tbTotNF.Text,Decimal) &"','" &CType(tbVencimento.Text, DateTime) &"','" & ddlBancoCob.SelectedItem.Text &"','" & ddlBancoCCorrente.SelectedItem.Text &"','" &CType(tbDesconto.Text,Decimal) &"','" &CType(tbLiquido.Text,Decimal) &"','" &CType(tbLiquido.Text,Decimal) &"','" & lblIUSER.Text &"','" &CType(lblIData.Text,Date) &"')"

Dim Conex?oAs SqlConnection =New SqlConnection("Data Source=SERV-IPE2;Initial Catalog=IpeSys;Persist Security Info=True;User ID=ASPNET;Password=IpeSys")

Dim ComandoSqlAsNew SqlCommand(SQL, Conex?o)

Conex?o.Open()

Dim sAsString

s ="25/12/2005"

Dim dAs DateTime

d = DateTime.ParseExact(s,"dd/MM/yyyy",Nothing)

ComandoSql.ExecuteNonQuery()

Conex?o.Close()

Response.Redirect("Lista.aspx")

EndSub

ProtectedSub CancelButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

Response.Redirect("Lista.aspx")

EndSub


But I have the same problem with the out-of-range error. Can you explain me more about your code??
Thanks a lot.
Raphael

The conversion of a char data type to a datetime data type resulted in an out-of-range dat

getting this error - i think its because im trying to put english date
format into american date format
(asp to sql server 2000)

im getting the date using
mortsourcedlabel.Text = Date.Today
mortsourcedlabel.Visible = True
mortsourced.Visible = False

which throws the date out (in uk format) to 13/02/2004 - all well and good
but -
im assuming that its going out of range as sql needs 02/13/2004
heh im guessing ive been testing code the past 2 weeks -- and now its the
13th which has now thrown up the error!
how do i change the date format not in the code above (i still need to see
the date in english-UK format on the page) but before the sql statement
(maybe in my stored procedure?)
thanks

marknm i sussed it with :-

Dim msl As String = mortsourcedlabel.Text.ToString ' date
Dim day, month, year As String
If mortsourcedlabel.Text.ToString = "" Then
msl = "01/01/1900"
Else
Dim mydate As Date
mydate = mortsourcedlabel.Text.ToString
day = mydate.Day.ToString
month = mydate.Month.ToString
year = mydate.Year.ToString
msl = month + "/" + day + "/" + year
End If

cheers

mark

The conversion of a char data type to a datetime data type resulted....

Hi,

I'm trying to insert a date time value into a SQL Server Database field of type date time using a c# web form.
I get the following error...
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated"

My SQL looks like this:

INSERT INTO tblname (fldname) VALUES ('" + DateTime.Now.ToString() + "')"

What am I doing wrong?Remove the ToString() method as that is changing the DateTime type to a string (or Char type) ...at least thats what it looks like.
Nope sorry to say that didn't work.

My C# now looks like

SqlConnection myConn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["eTeam_ConnectionString"].ToString());
string mySQL = "INSERT INTO tblname (datestamp) VALUES ('" + DateTime.Now + "')";
SqlCommand myCMD = new SqlCommand(mySQL, myConn);
myConn.Open();
myCMD.ExecuteNonQuery();
myConn.Close();
myCMD.Dispose();
myConn.Dispose();

And the field 'datestamp' is a SQL Server Data Type datetime

Anyone else?
You can use getDate() method of SQL server to update date.

INSERT INTO tblname (datestamp) VALUES (getDate())";
Thanks for that - definately half way to solving my problem.
What if I need to take a value from an ASP:Calendar control?
Is there a ToDate() function that will convert it as required?
You can use

Calendar1.SelectedDate.ToShortDateString())

which will convert this date into short date format.