troubleshooting Question

A datepart problem!!

Avatar of DamMadsen
DamMadsen asked on
Microsoft SQL Server
6 Comments1 Solution656 ViewsLast Modified:
First of all, my English is not perfect, but i hope you guys (girls?) understand my question.

I have a field with datatype varchar where we stored dates in the format YYYY/MM/DD (ex. 2002/11/08). If the date is unknown we store 0000/00/00 and if we know only the year and month we store YYYY/MM/32.
The reason for storing the date in this format is that we the system originally was build for another database-system.

And this system works fine, but if I want to use datepart to convert the date to week it is only possible if the date is "valid" otherwise it returns an error.

EX.
SELECT datepart(wk, '2001/07/10') returns 28
SELECT datepart(wk, '2001/07/32') return error
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Likewise some of our dates are "wrong" ex. 2001/09/31 which of cause don't exists since there is only 30 days in september
SELECT datepart(wk, '2001/09/31') return error
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."


Oki, then what i want to know is if it is possible to "ignore" the error. When i run statement that return ex. 100 rows with datepart(wk, date_field) the whole statement doesn't run if only one of the dates is not valid?

So i want it to continue the statement and ignore the date-errors og maybe there are en entirely different way to do it?

Hope you understand my English og of cause my problem.


ASKER CERTIFIED SOLUTION
Flynnious

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros