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.

The JScript "getYear" does not work in Mozilla ? :-)))

Hi. I have a header implemented as a user control (ascx). On start-up, I
show the date/time (in a label named "lblTimer") like this:

<html>
......
<span...
<%=Now.ToShortDateString() %> - <%=ctype(Now.ToLongTimeString(),
datetime).tostring("H:mm:ss tt") %>
</span
Then, I have a JavaScript block which refreshes the clock every second - and
the main function is:
__________________________________________________ __________________________
function ClockRefresh(){

var dtDate=new Date();
var strAMPM="AM";
var intHours=dtDate.getHours();
var str = "";

str += (dtDate.getMonth() + 1) + "/";
str += dtDate.getDate() + "/";
str += dtDate.getYear();

if(intHours>12){
intHours-=12;
strAMPM="PM";
}

var intMinutes=dtDate.getMinutes() + "";
if(intMinutes.length==1){
intMinutes="0" + intMinutes;
}

var intSeconds=dtDate.getSeconds() + "";
if(intSeconds.length==1){
intSeconds="0" + intSeconds;
}

lblTimer.innerHTML=str + " - " + intHours + ":" + intMinutes + ":" +
intSeconds + " " + strAMPM;
}
__________________________________________________ __________________________

In IE everything is fine. In Mozilla, however, it starts for instance as
2/24/2005 - 1:06:48 AM
but the next second, when ClockRefresh executes, I get
2/24/105 - 1:06:49 AM

There is a 1900 years difference between the JScript getYear in Mozilla and
the one in IE !

Thank you, Alex."Alex Nitulescu" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in message
news:u54CugjGFHA.1392@.TK2MSFTNGP10.phx.gbl...

> There is a 1900 years difference between the JScript getYear in Mozilla
> and the one in IE !

Try getFullYear() instead...

http://www.quirksmode.org/js/datecompat.html
Thanks, it works fine now !

"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:%23ljNzbkGFHA.3068@.tk2msftngp13.phx.gbl...
> "Alex Nitulescu" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in message
> news:u54CugjGFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> There is a 1900 years difference between the JScript getYear in Mozilla
>> and the one in IE !
> Try getFullYear() instead...
> http://www.quirksmode.org/js/datecompat.html

The JScript "getYear" does not work in Mozilla ? :-)))

Hi. I have a header implemented as a user control (ascx). On start-up, I
show the date/time (in a label named "lblTimer") like this:
<html>
.....
<span...
<%=Now.ToShortDateString() %> - <%=ctype(Now.ToLongTimeString(),
datetime).tostring("H:mm:ss tt") %>
</span>
Then, I have a JavaScript block which refreshes the clock every second - and
the main function is:
________________________________________
____________________________________
function ClockRefresh(){
var dtDate=new Date();
var strAMPM="AM";
var intHours=dtDate.getHours();
var str = "";
str += (dtDate.getMonth() + 1) + "/";
str += dtDate.getDate() + "/";
str += dtDate.getYear();
if(intHours>12){
intHours-=12;
strAMPM="PM";
}
var intMinutes=dtDate.getMinutes() + "";
if(intMinutes.length==1){
intMinutes="0" + intMinutes;
}
var intSeconds=dtDate.getSeconds() + "";
if(intSeconds.length==1){
intSeconds="0" + intSeconds;
}
lblTimer.innerHTML=str + " - " + intHours + ":" + intMinutes + ":" +
intSeconds + " " + strAMPM;
}
________________________________________
____________________________________
In IE everything is fine. In Mozilla, however, it starts for instance as
2/24/2005 - 1:06:48 AM
but the next second, when ClockRefresh executes, I get
2/24/105 - 1:06:49 AM
There is a 1900 years difference between the JScript getYear in Mozilla and
the one in IE !
Thank you, Alex."Alex Nitulescu" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in message
news:u54CugjGFHA.1392@.TK2MSFTNGP10.phx.gbl...

> There is a 1900 years difference between the JScript getYear in Mozilla
> and the one in IE !
Try getFullYear() instead...
http://www.quirksmode.org/js/datecompat.html
Thanks, it works fine now !
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:%23ljNzbkGFHA.3068@.tk2msftngp13.phx.gbl...
> "Alex Nitulescu" <REMOVETHIScuca_macaii2000@.yahoo.com> wrote in message
> news:u54CugjGFHA.1392@.TK2MSFTNGP10.phx.gbl...
>
> Try getFullYear() instead...
> http://www.quirksmode.org/js/datecompat.html
>