IsDate Problem

[falkon_tech]

I am building an import program that reads a text file and inserts it into a SQL table.

One of the fields is a date. So I am doing an IsDate check on every record before it inserts. For some reason "0001-01-01" returns true on the isdate function but when it goes to the stored procedure to insert the record it errors.

Is there any way around this? I know its just a data entry error on the users side, but I cannot fix it and would like to have the import just enter a null value since the date is invalid.

Any thoughts?

Thanks in advance!

 

[jmcilhinney]

Does your database have a minimum value that it recognises as a valid date? That's the only reason i can see that would cause that error. If so then you'd need to hard-code a check against that date before entering the value into the database.

 

[falkon_tech]

i ended up just putting a check to see if the date value is less than the minimum value.

my question though, is why does the IsDate function return a True value, but SQL does not?

 

[TechGnome]

Because there's something builtin to SQL that says a date cannot be less than a specific value. Doesn't mean it's an invalid date... just there would be no way to accurately store it in the DB.

-tg