SQLDateTime overflow Delete issue?

I receive the error: "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM" when attempting to delete a record in my backend sql server 2005 database??
ShepweddAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
datetime wont support upto 9999 use value upto 2049-12-31
0
Aneesh RetnakaranDatabase AdministratorCommented:
sorry , mymistake it supports   January 1, 1753, through December 31, 9999..  seems like your database constain values that are out of range
0
ralmadaCommented:
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Aneesh RetnakaranDatabase AdministratorCommented:
SELECT *
FROM urTable
WHERE  ISDATE(urDateColumn) = 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShepweddAuthor Commented:
aneeshattingal,

Do you want me to run your query on my table?
0
ShepweddAuthor Commented:
No results are returned when I run your query.

I am setting the time value as well as the date value of my column if this causes an issue?
0
ShepweddAuthor Commented:
...and the time is being set in 24hr, i.e. a typical value that I'm wanting to delete is 15/07/2009 15:00:00. But obviously it's not being deleted as I get the error.
0
ralmadaCommented:
The problem is not in your table but in the code you're passing from ASP. Have you checked the link I've suggested above?
 
http://forums.asp.net/t/1134244.aspx 
Here's another one:
http://p2p.wrox.com/asp-net-3-5-basics/73673-sqldatetime-overflow.html 
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
What is the datatype of that column either datetime or smalldatetime?

smalldatetime wont support that value. It supports till 06-06-2079 only.

aneesh,
    SELECT *
FROM urTable
WHERE  ISDATE(urDateColumn) = 0

This will work fine for both datetime and smalldatetime.

Shepwedd,
   Can you kindly confirm this one
0
ralmadaCommented:
 

I still think the problem is not in the table. If I understood correctly he's trying to delete a record, so the record is already in the table and is valid date. i.e. doing a select with ISDATE will not return any records because the table contains valid dates.

I feel the problem is with the parameter being received from ASP. Probably the asker is sending a '01/01/0001' date which is not supported in SQL. Again, have a look at the links I've suggested.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.