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 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy