Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Error conversion DateTime 3

Status
Not open for further replies.
cumap

cumap

IS-IT--Management
Jul 9, 2007
268
US
Hi again,

I'm getting this error while saving datetime (in string) entered by user into datetime field in a database.


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.


To be more specific: the query works fine with most "real life" datetime, only when I entered this "11/11/1111" the ERROR happens.

Is there a way to Catch this error?
 
Sort by date Sort by votes
If you are saving it as a string, check into CAST and CONVERT in BOL.

Always remember that you're unique. Just like everyone else.
 
Upvote 0 Downvote
Based on your original posting, you're either looking for a way to FIX the input so you don't get an error, or you're looking for a way to CATCH the error and report it back. More detail would (Tom Cruise moment) "help us help you."

If you haven't read it yet, this link provides some excellent suggestions on how to get better answers to questions:
And since the other guys are trying to help you FIX the input, lemme toss this out:

For SQL Server 2000, check out "@@ERROR" - for 2005 check out "TRY...CATCH" in SQL Server Books On Line (BOL) for ideas on how to actually CATCH (trap) errors.

< M!ke >
[small]"Oops" is never a good thing.[/small]
 
Upvote 0 Downvote
To George,
I tried IsDate function and it found my string '11/11/1111' is TRUE, but the error is still there. So, I don't think whether my input is a date, the question is more about why this particular string is not exceptable to the field.
 
Upvote 0 Downvote
Uh... No. IsDate did NOT return true.

Code: 
Select IsDate('11/11/1111')

You will see that the output of this function is 0, which means false.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Because SQL Server doesn't accept dates from the 12th century. Jan 1 1753 is the earliest date it can store. By the way, the SQL Server IsDate function returns 0 on that date.
 
Upvote 0 Downvote
cumap,

You may find this interesting (and perhaps helpful).
faq183-6419



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
How are you entering this value in the database? Do you have a client program like MS Access? If there is an application program, you should ALSO (in addition to using IsDate to avoid actually trying to insert bad dates) make the client reject bad dates before it even tries to put them in the database.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

The best part about anything that has cheese is the cheese.[/color]
 
Upvote 0 Downvote
I'm learning and testing CAST and CONVERT

both of them give me this error:


Type mismatch: 'convert'
or
Type mismatch: 'cast'


Is this query correctly set
Code: 
sql = "UPDATE tblRMA SET " _
			& "		RMA_No 			= '" & rmano & "', " _
			& "		CompanyName 	= '" & cname & "', " _
			& "		Date_Returned 	= '" & date_ret & "', " _
			& "		ContactName 	= '" & contactN & "', " _
			& "		Phone_No 		= '" & phone & "', " _
			& "		ProblemTxt 		= '" & problemtxt & "', " _
			& "		Date_Purchased 	= '" & convert(datetime,dateEntered,101) & "', " _
			& "		Priority 		= " & priority & ", " _
			& "		Confirmed_DOP 	= " & confirmed & ", " _
			& "		Status			= '" & rma_status & "' " _
			& "	WHERE ID = " & id
 
Upvote 0 Downvote
to RiverGuy:

I think you're right because I did a test with the given time you mentioned and the error does pop up for any year before that.

Now, even I KNOW my users are not stupid and they DARE NOT to give any weird date like that, but I do want to learn on how to avoid such thing in a simpliest way.

By the way, guys... I am slowly going through all of your inputs in my thread here and learn and test your method one by one. Will come back to you all a little later.

Thanks again.
 
Upvote 0 Downvote
ah, I forgot...

here is how I got this isDate=TRUE

Code: 
response.write isDate(Replace(Server.HTMLEncode(Request("date_pur")), "'", "''"))
 
Upvote 0 Downvote
Thanks George for the link tutorial on ISDATE and CONVERTING... I think it would be very helpful knowing such technique in the future.

However, I now know why the error is occurred and by stopping it from happen again, I more need to know or find a way to make sure users don't enter such bad inputs again. In another word. Validation is what I will need to do make sure the year digits is not smaller than number 1753. That will solve this problem, I guess!
 
Upvote 0 Downvote
To ESquared,

you're right!

Like I said in the latest message, what I need now is to make sure user won't entered bad inputs. I specifically noted (mm/dd/yyyy) right next to the date input. I previously saved the data as varchar, but have to changed it to datetime due to a request that users will be able to search the data as a date.

I don't know "client program" you mentioned, but I do utilize SQL v.8, and I don't know much about creating function in SQL like many helps have suggested.

Well, I think we made a progress here now that I understand why I got this error and what I need to do to solve it. Thank you all for your enthusiastic help.

Have a good day!
 
Upvote 0 Downvote
Your client program is a browser running an asp application.

Use a regular expression in the browser to validate the field before you submit it.

The IsDate() the folks in this thread were talking about is a SQL Server function. IsDate() in VB is going to give potentially different answers.

So you have to check IsDate() as well as some other stuff:

Code: 
Dim Dt
Dt = Request("date_pur")
If IsDate(Dt) And (Year(Dt) >= 1753 or Year(Dt) < 9999) Then 
   'it's a date.
End if
It won't make a difference in this case, but logically you should check for whether it is a date before escaping single quotes. And you would definitely not HTMLEncode something, THEN replace single quotes, THEN check to see if it's a date. If you HTMLEncode it, the target is a web browser so you don't care about single quotes and HTMLEncoding what is otherwise a date could break it!. And if single quotes are valid in the date, then you have to check whether they are valid before you double them up.
 
Upvote 0 Downvote
While we are on the subject of data validation, I would suggest that you might want to be a little more restrictive than what a DateTime data type will allow.

A DateTime has a range of 1753 to year 9999. It appears as though you are dealing with a purchase date. Depending on the application and what this date represents, you may want to restrict it to >= Today's date and < today's date + 1 or 2 years. The point I am trying to make is that you should restrict it to something reasonably small.

For example, if you were tracking birthdays, valid dates might be >= 130 years ago and less than or equal to today. I can't tell you how many times I have tried to import data in to a DB where the birthdate is something like 1/2/1790. In this case, the user transposed a couple of the numbers (it should really have been 1/2/1970 instead).

The more restrictive you are with your validation, the less likely there will be data enry errors, and the more likely you will have a database full of good, useful, information.

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I'd like to thank you all of your help in this thread, especially to ESquared and George for the last 2 very detail messages. I'd certainly take them into account for this assignment and to other next assignments in the future.

You all are great!!! Thank you again!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
624
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
Error in Select query
Replies
14
Views
843
Andrzejek
Andrzejek
Virendra Yaduvanshi
  • Locked
  • Question
SSRS - Subscription page error
Replies
0
Views
367
Virendra Yaduvanshi
Virendra Yaduvanshi
new_to_sql
  • Locked
  • Question
Access SQL Query Error
Replies
5
Views
381
dhookom
dhookom
G12Consult
  • Locked
  • Question
Implicit conversion from data type error
Replies
14
Views
1K
Olaf Doschke
Olaf Doschke

Part and Inventory Search

Sponsor

Back
Top