datefield problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

datefield problem

Author

Topic

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 12:41:51

Hi All,

I am developing asp.net c# application . I am trying to insert data into datefield. it works fine with few dates but with somedates it gives me folowing error.

System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.

i am just wondering if someone can help me out.

thanks,
Kiran

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-27 : 12:53:43

Your dates are most likely passed as character values to the database and have formats inconsistent with the DATEFORMAT settings in the SQL database, because the implicit conversion to date will result in an incompatible date value.

ex.
'07/27/2004' --sent as mm/dd/yyyy will fail if server settings are DMY
'27/07/2004' --sent as dd/mm/yyyy will fail if server settings are MDY

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 14:50:08

thanks for ur reply.
i am doing exactly what u said.Also,I am entering string as date.
Do u know wt i am doing wrong?
thanks,
kiran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 15:14:13

What dates specifically are failing?

Tara

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:21:45

Thanks for ur reply.

date is 05/15/1970.

thanks
kiran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 15:25:38

So what setting do you have for SET DATEFORMAT? You probably have dmy, which is invalid since 15 isn't a valid month.

Tara

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:27:48

My dateformat is MDY.

That's problem here

i used following query for dateformat

set dateformat MDY;

thanks
kiran

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 15:30:39

So what happens when you run the stored procedure or query in Query Analyzer and pass in that date?

Tara

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:37:07

it is a kind of weird. it deosn't give me any error. when i run this query in query analyzer but when i pass this date in my application it gives me following error.

System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated

thanks,
kiran

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:37:08

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:37:24

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 15:38:51

You'll need to post the code then.

Tara

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 15:44:43

MY insert statement is as follows:

String userUniqueInfoSQL = "Insert into dbo.CUSTOMER values('";
userUniqueInfoSQL += lastName+"','";
userUniqueInfoSQL += firstName+"','";
userUniqueInfoSQL += middleName+"','";
userUniqueInfoSQL += SSN+"',";
userUniqueInfoSQL += DOB+",'";
userUniqueInfoSQL += pEmail+"','";
userUniqueInfoSQL += pass+"','";
userUniqueInfoSQL += Q1+"','";
userUniqueInfoSQL += A1+"','";
userUniqueInfoSQL += Q2+"','";
userUniqueInfoSQL += A2+"','"+echoID+"')";

thanks very much
Kiran

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-27 : 16:11:06

I am surprised any worked here.

userUniqueInfoSQL += SSN+"',";
userUniqueInfoSQL += DOB+",'";

looks to me like it builds: 123456789', 5/15/1970,'... and the date would need to be passed with single quotes around it.

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 17:27:57

thanks for ur reply.
Actually I tried with single quotes around it but it still gives same error

Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.

plz help me out!

thanks
kiran

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-27 : 17:44:55

MY error has been changed now with single quotes. i am getting this error now

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

plz help me out
thanks,
kiran

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-28 : 13:07:05

What are some examples of dates that work?

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-07-28 : 13:09:09

Thanks very much for your help. I resolved my problem.

Thanks,
Kiran

sinsai
Starting Member

2 Posts

Posted - 2004-08-10 : 17:09:28

kirannatt...what did you do to resolve your problem?

quote:
Originally posted by kirannatt

Thanks very much for your help. I resolved my problem.

Thanks,
Kiran

**********************
sinsai

kirannatt
Yak Posting Veteran

66 Posts

Posted - 2004-08-10 : 17:13:58

I just put date field value in single quote. and I changed dateformat to MDY in sqlserver.

I hope this will help.

Kiran

amsterdamgj
Starting Member

1 Post

Posted - 2006-06-27 : 06:48:57

quote:
Originally posted by kirannatt

Hi All,

I am developing asp.net vb application . sometimes the date can be empty and sometimes it should be filled in. i wrote a function to do that, as follow:
Function fmt(ByVal mat As Object) As Object
If mat = "-" Then mat = "1-1-1900" Else
Return Format(CDate(mat), "dd-MM-yyyy")
End Function
"i initialize the textbox="-". here mat is textbox"
but when i submit, an error message will be displayed

error message: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.

i am just wondering if someone can help me out.

thanks,
Kiran

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources