DamMadsen
asked on
A datepart problem!!
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.
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.
Maybe try this:
select case when isdate('2001/07/32') = 1 then datepart(wk, '2001/07/32') else 0 end as weekValue1
select case when isdate('2001/07/32') = 1 then datepart(wk, '2001/07/32') else 0 end as weekValue1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This might help get you started on checking every possoble part of your date to see if it is valid.
Copy it into Query Analyzer And Execute
ADW
DECLARE @date VARCHAR(10)
SELECT @date = '2004/02/29'
IF ISDATE(@date) = 1
BEGIN --This is a valid Year, Month, Day
PRINT 'Valid Year/Month/Day'
END
ELSE IF ISDATE((REVERSE(SUBSTRING(
BEGIN --This is a valid Year and Month
PRINT 'Valid Year/Month Only'
END
ELSE IF ISDATE(LEFT(@date, PATINDEX('%/%', @date)) + '01' + RIGHT(@date, PATINDEX('%/%', REVERSE(@date)))) = 1
BEGIN --This is a valid Year and Day only
PRINT 'Valid Yeay and Day Only'
END
ELSE IF ISDATE(LEFT(@date, PATINDEX('%/%', @date) - 1) + '/01/01') = 1
BEGIN --This is a valid year only
PRINT 'Valid Year Only'
END
ELSE IF ISDATE('2000/' + RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date))) = 1
BEGIN --This is a valid month/day only
PRINT 'Valid Month/Day Only'
END
ELSE IF ISDATE('2000/01' + RIGHT(@date, PATINDEX('%/%', REVERSE(@date)))) = 1
BEGIN --This is a valid day only
PRINT 'Valid Day Only'
END
ELSE IF ISDATE('2000/' + LEFT(RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date)), PATINDEX('%/%', RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date)))) + '01') = 1
BEGIN --This is a valid Month only
PRINT 'Valid Month Only'
END
ELSE
BEGIN
PRINT 'Not a Valid Year, Month, Or Day'
END
Copy it into Query Analyzer And Execute
ADW
DECLARE @date VARCHAR(10)
SELECT @date = '2004/02/29'
IF ISDATE(@date) = 1
BEGIN --This is a valid Year, Month, Day
PRINT 'Valid Year/Month/Day'
END
ELSE IF ISDATE((REVERSE(SUBSTRING(
BEGIN --This is a valid Year and Month
PRINT 'Valid Year/Month Only'
END
ELSE IF ISDATE(LEFT(@date, PATINDEX('%/%', @date)) + '01' + RIGHT(@date, PATINDEX('%/%', REVERSE(@date)))) = 1
BEGIN --This is a valid Year and Day only
PRINT 'Valid Yeay and Day Only'
END
ELSE IF ISDATE(LEFT(@date, PATINDEX('%/%', @date) - 1) + '/01/01') = 1
BEGIN --This is a valid year only
PRINT 'Valid Year Only'
END
ELSE IF ISDATE('2000/' + RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date))) = 1
BEGIN --This is a valid month/day only
PRINT 'Valid Month/Day Only'
END
ELSE IF ISDATE('2000/01' + RIGHT(@date, PATINDEX('%/%', REVERSE(@date)))) = 1
BEGIN --This is a valid day only
PRINT 'Valid Day Only'
END
ELSE IF ISDATE('2000/' + LEFT(RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date)), PATINDEX('%/%', RIGHT(@date, LEN(@date) - PATINDEX('%/%', @date)))) + '01') = 1
BEGIN --This is a valid Month only
PRINT 'Valid Month Only'
END
ELSE
BEGIN
PRINT 'Not a Valid Year, Month, Or Day'
END
I agree that there is no way to ignore the error. But since you know what data errors will occur, you could code to handle them with appropriate checks. For example:
DECLARE @maxDaysInMth VARCHAR(25)
SET @maxDaysInMth = ' 312831303130313130313031'
SELECT CASE
WHEN SUBSTRING(yourDate, 9, 2) > SUBSTRING(@maxDaysInMth, CAST(SUBSTRING(yourDate, 6, 2) AS INT) * 2, 2)
THEN DATEPART(WEEK, LEFT(yourDate, 8) + SUBSTRING(@maxDaysInMth, CAST(SUBSTRING(yourDate, 6, 2) AS INT) * 2, 2))
ELSE DATEPART(WEEK, yourDate)
END
FROM yourTable
WHERE yourDate <> '0000/00/00'
This code checks if the day is greater than the max allowed for that month; if so, it changes it to the max.
You would of course have to add code to handle leap years, if desired.
DECLARE @maxDaysInMth VARCHAR(25)
SET @maxDaysInMth = ' 312831303130313130313031'
SELECT CASE
WHEN SUBSTRING(yourDate, 9, 2) > SUBSTRING(@maxDaysInMth, CAST(SUBSTRING(yourDate, 6, 2) AS INT) * 2, 2)
THEN DATEPART(WEEK, LEFT(yourDate, 8) + SUBSTRING(@maxDaysInMth, CAST(SUBSTRING(yourDate, 6, 2) AS INT) * 2, 2))
ELSE DATEPART(WEEK, yourDate)
END
FROM yourTable
WHERE yourDate <> '0000/00/00'
This code checks if the day is greater than the max allowed for that month; if so, it changes it to the max.
You would of course have to add code to handle leap years, if desired.
ASKER
In fact all the answers where very usable and thanks alot, but found the most help in this answer.
SELECT datepart(wk, REPLACE( '2001/07/32' , '/32', '/01' )
Unfortunately, you cannot "ignore" this error, you must "filter" out or modify these values to have a valid transformation in 100% of the cases...
CHeers