I have column date with string(nvarchar) format. and I have inconsistent column like this
I have more example like this
2021-7-01
2021-8-01
first I have try with CASE WHEN like this
SELECT
CASE
WHEN DATE_IN LIKE '[0-9]{4}-[0-9]{2}-[0-9]{2}'
THEN CONVERT(datetime, date_in, 102)
WHEN date_in LIKE '[0-9]{4}-[0-9]{2}-[0-9]{2}'
THEN CONVERT(datetime, date_in) END AS DATE_IN, EMP_NAME,DATE_IN
FROM staging.irisEtcSingleFile
but, the output is NULL .. I want the output is like this
2021-01-01
2021-07-01
2021-08-01
I really appreciate to get the answer .. Thanks before
CodePudding user response:
Try this snippets, produces a good response:
create table #t (txt varchar(100))
delete from #t
go
insert into #t select '2021-7-01'
insert into #t select '2021-8-01'
insert into #t select '2021-01-01'
insert into #t select '2021-07-01'
insert into #t select '2021-2-02'
insert into #t select '2021-08-01'
go
select
txt
from
#t
where
txt not like '%[^0-9-]%' and
len(replace(txt, '-', '')) = len(txt)-2 and
txt like '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
Output:
2021-01-01
2021-07-01
2021-08-01
CodePudding user response:
Check this query using
replicate
SELECT
CASE
WHEN test_col LIKE REPLICATE('[0-9]',4) '-[0-9]-' REPLICATE('[0-9]',2)
THEN CONVERT(datetime, test_col, 102)
WHEN test_col LIKE REPLICATE('[0-9]',4) '-' REPLICATE('[0-9]',2) '-' REPLICATE('[0-9]',2)
THEN CONVERT(datetime, test_col) END AS test_col
FROM test