Question about reasonable dates 1

[TysonLPrice]

I occasionally get an error "The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error" in the system I support.

It is usually caused by dates that were mis-keyed and are far in the future like today I had two, 5/28/2204 and 5/28/5004.

The difference in the date formats is the definition in the Visual basic application and it updating SQL 2000.

Just when does SQL consider a date too far in the future? If that is really the cause, but I'm guessing about that.

 

[amorous]

if you miskey even something like this below:

5/28/204 or 528/2004 it gives the same error

it is not just with the date being too far...

i guess the error is because of the invalid entry

-VJ

 

[TheDrider]

Are you using a smalldatetime field?

Datetime = 1 Jan 1753 to 31 Dec 9999
smalldatetime = 1 Jan 1900 to 6 Jun 2079

 

[TysonLPrice]

thedrider,

That answers my question. Yes there is a mismatch. I'm using Dim RequestDate as Date in Visual Basic and smalldatetime in SQL. I'm not aware of any other format I could use in VB.

The reason I posted the question is I intend to test for the greatest date SQL can handle and prevent entry.

I'll talk to my team leader about changing to Datetime but that's not my call and I don't know enough about the differences to know the impact.

As a side note I'm dealing mostly with birthdays and I could code a birthday can't be greater then today. But, there are future dated fields in our system also.

Thanks!

 

[SQLSister]

Anything historical in nature should be validated on the client to make sure it is not greater than the current date. For future type dates I would write a function validating against the dates in smalldatetime on the client as well. No point wasting network time sending data that will never insert. Make sure you do ththese validations in some kind of class or function so they can be reused throughout the site.

Questions about posting. See faq183-874