November 19, 2018 at 3:59 am
Hi,
I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
November 19, 2018 at 4:37 am
You've posted in the SQL Server 2008 forum - is that the actual version you're using? If you're on SQL Server 2012 or above, you can use TRY_CONVERT. If you really are on 2008, TRY...CATCH might work, but you'd have to do it one row at a time.
John
November 19, 2018 at 4:59 am
pwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
November 19, 2018 at 5:42 am
Jonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATE
November 19, 2018 at 6:16 am
pwalter83 - Monday, November 19, 2018 5:42 AMJonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATE
Or you could have:UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1
THEN CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' END
That would convert valid dates that aren't in yyyy-mm-dd format.
It would also solve the months that only have 30 days and the non-leap year February problem with your code.
November 19, 2018 at 6:24 am
Jonathan AC Roberts - Monday, November 19, 2018 6:16 AMpwalter83 - Monday, November 19, 2018 5:42 AMJonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATEOr you could have:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1
THEN CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' END
due care should be taken with isdate.
any of these will happily state it is a valid date when in most likelihood they should not beselect isdate('2001')
, isdate('200101')
, isdate('010101')
, isdate('4040') -- common if input was Mainframe COBOL
, isdate('2020') -- common if input was Intel COBOL
November 19, 2018 at 6:30 am
frederico_fonseca - Monday, November 19, 2018 6:24 AMJonathan AC Roberts - Monday, November 19, 2018 6:16 AMpwalter83 - Monday, November 19, 2018 5:42 AMJonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATEOr you could have:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1
THEN CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' ENDdue care should be taken with isdate.
any of these will happily state it is a valid date when in most likelihood they should not beselect isdate('2001')
, isdate('200101')
, isdate('010101')
, isdate('4040') -- common if input was Mainframe COBOL
, isdate('2020') -- common if input was Intel COBOL
This should make it so only dates within a valid range are accepted:UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1 THEN
CASE WHEN CONVERT(datetime,RELEASE_DATE) BETWEEN '1900-01-01' AND '2030-01-01' THEN
CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' END
ELSE '1900-01-01' END
The OP's code won't deal with the dates you've put either.
November 19, 2018 at 6:55 am
Jonathan AC Roberts - Monday, November 19, 2018 6:30 AMfrederico_fonseca - Monday, November 19, 2018 6:24 AMJonathan AC Roberts - Monday, November 19, 2018 6:16 AMpwalter83 - Monday, November 19, 2018 5:42 AMJonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATEOr you could have:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1
THEN CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' ENDdue care should be taken with isdate.
any of these will happily state it is a valid date when in most likelihood they should not beselect isdate('2001')
, isdate('200101')
, isdate('010101')
, isdate('4040') -- common if input was Mainframe COBOL
, isdate('2020') -- common if input was Intel COBOLThis should make it so only dates within a valid range are accepted:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1 THEN
CASE WHEN CONVERT(datetime,RELEASE_DATE) BETWEEN '1900-01-01' AND '2030-01-01' THEN
CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' END
ELSE '1900-01-01' END
The OP's code won't deal with the dates you've put either.
Yes the OP code won't deal with them - neither will the one you just posted.
select t.input
, isdate(t.input) as isdate
, case
when isdate(input) = 1
then case
when convert(datetime, input) between '1900-01-01' and '2030-01-01'
then convert(varchar(10), input, 121)
else '1900-01-01'
end
else '1900-01-01'
end validated
, t.expected
, convert(date, t.input) as actual_converted
from (values ('2001', '1900-01-01')
, ('200101', '1900-01-01')
, ('010101', '1900-01-01')
, ('4040', '1900-01-01')
, ('2020', '1900-01-01')
) t (input, expected)
Note that this will only matter if any of the OP's input data is less than 8 (or 10 if input contains
November 19, 2018 at 7:05 am
frederico_fonseca - Monday, November 19, 2018 6:55 AMJonathan AC Roberts - Monday, November 19, 2018 6:30 AMfrederico_fonseca - Monday, November 19, 2018 6:24 AMJonathan AC Roberts - Monday, November 19, 2018 6:16 AMpwalter83 - Monday, November 19, 2018 5:42 AMJonathan AC Roberts - Monday, November 19, 2018 4:59 AMpwalter83 - Monday, November 19, 2018 3:59 AMHi,I have a requirement to change the date to 1900-01-01 if its an invalid date like 2009-13-31 (Invalid month) or 2009-12-33 (Invalid date) or 2009-02-30 (Invalid date for February) or if the year exceeds 9999 or is less than 1900 (1900-9999).
In all the cases above the requirement is to change the date to 1900-01-01.
Could you somebody please have any suggestions / ideas how this can be handled ? Thanks for your help.
Where are these dates stored? On a table? In a varchar column?
Thanks Jonathan,
The dates were stored in a table in a varchar column.
I have now been able to resolve this with the following query. Thanks for your help.
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATEOr you could have:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1
THEN CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' ENDdue care should be taken with isdate.
any of these will happily state it is a valid date when in most likelihood they should not beselect isdate('2001')
, isdate('200101')
, isdate('010101')
, isdate('4040') -- common if input was Mainframe COBOL
, isdate('2020') -- common if input was Intel COBOLThis should make it so only dates within a valid range are accepted:
UPDATE myTable
SET RELEASE_DATE = CASE WHEN ISDATE(RELEASE_DATE)=1 THEN
CASE WHEN CONVERT(datetime,RELEASE_DATE) BETWEEN '1900-01-01' AND '2030-01-01' THEN
CONVERT(varchar(10), RELEASE_DATE, 121)
ELSE '1900-01-01' END
ELSE '1900-01-01' END
The OP's code won't deal with the dates you've put either.Yes the OP code won't deal with them - neither will the one you just posted.
select t.input
, isdate(t.input) as isdate
, case
when isdate(input) = 1
then case
when convert(datetime, input) between '1900-01-01' and '2030-01-01'
then convert(varchar(10), input, 121)
else '1900-01-01'
end
else '1900-01-01'
end validated
, t.expected
, convert(date, t.input) as actual_converted
from (values ('2001', '1900-01-01')
, ('200101', '1900-01-01')
, ('010101', '1900-01-01')
, ('4040', '1900-01-01')
, ('2020', '1900-01-01')
) t (input, expected)Note that this will only matter if any of the OP's input data is less than 8 (or 10 if input contains
Frederico, Thanks for the test data. There is nothing like a good bit of test driven development.
I meant to put " CONVERT(varchar(10),CONVERT(datetime, input), 121)" in my original query.
Anyway this works on the test data you've provided:
SELECT
t.input,
ISDATE(t.input) AS isdate,
CASE
WHEN ISDATE(input) = 1 AND len(input) >=8
THEN CASE
WHEN CONVERT(datetime, input) BETWEEN '1900-01-01' AND '2030-01-01'
THEN CONVERT(varchar(10),CONVERT(datetime, input), 121)
ELSE '1900-01-01'
END
ELSE '1900-01-01'
END AS validated,
t.expected,
CONVERT(date, t.input) AS actual_converted
from (values ('2001', '1900-01-01')
, ('200101', '1900-01-01')
, ('010101', '1900-01-01')
, ('4040', '1900-01-01')
, ('2020', '1900-01-01')
) t (input, expected)
November 19, 2018 at 7:16 am
Rather than depending on proper string -> date conversion at time of query, you can create a check constraint on the table column, insuring that date strings are encoded with a valid date and are in expected format.
For example:
CREATE table foo (
foo_date varchar(30) not null
constraint ck_foo_StringDateValidation
check (foo_date = convert(char(10),cast(foo_date as datetime),126))
);
-- valid date and format
insert into foo (foo_date) values ('2009-12-31');
(1 row affected)
-- invalid date
insert into foo (foo_date) values ('2009-13-31');
Msg 242, Level 16, State 3, Line 8
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
-- valid date, but not in format you're wanting
insert into foo (foo_date) values ('2009/13/31');
Msg 242, Level 16, State 3, Line 15
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 14, 2022 at 1:36 pm
How do we add leap years in the following query along with the month of February?
CASE
WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01'
WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATE
January 15, 2022 at 5:05 pm
How do we add leap years in the following query along with the month of February?
CASE WHEN SUBSTR(RELEASE_DATE,1,4)<1900 OR SUBSTR(RELEASE_DATE,1,4)>9999 THEN '1900-01-01' WHEN SUBSTR(RELEASE_DATE,6,2)>12 THEN '1900-01-01' WHEN SUBSTR(RELEASE_DATE,9,2)>31 THEN '1900-01-01' WHEN SUBSTR(RELEASE_DATE,6,2)=2 AND SUBSTR(RELEASE_DATE,9,2)>29 THEN '1900-01-01' ELSE RELEASE_DATE
So what version of SQL Server are YOU using?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2022 at 6:20 am
Actually I'll be executing this in Teradata as my database is only available there and also the version is 15.0 for the Teradata.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply