select * from AOFDOXF DOXF
LEFT JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE IDXT.IXXMLK = 'DATE'
AND DOXF.DXINNR = 312100

--dd/MM/yyyy
--dd/MM/yyyy HH:mm:ss
-- ??? 12421	1	20144-06-30   , 462722	1	0066-01-17

select IXIXID FROM AOFIDXT WHERE IXXMLK = 'DATE';

select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND NOT(DOXF.DXIXDA like '%/%')
AND NOT(DOXF.DXIXDA like '%-%')

--NA , na , ou vide

select top 10 * from AOFDOXF DOXF;
select top 1000 * from AOFIDXT;


select count(*) FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE IDXT.IXXMLK = 'DATE'
AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');

-- get values to be cleaned up
select COUNT(*) FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE IDXT.IXXMLK = 'DATE'
AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');


-- get innr values to be cleaned up
select DOXF.DXINNR , DOXF.DXDONR FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE IDXT.IXXMLK = 'DATE'
AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');

--get DATE values containing / or -
select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND ((DOXF.DXIXDA like '%/%')
OR (DOXF.DXIXDA like '%-%'))



--count/lister des dates qui ne sont pas sous format yyyy-MM-dd ??

select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null
order by DOXF.DXIXDA;

select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null;
--AND DOXF.DXIXDA like '%-%'


select top 10 DOXF.DXINNR  FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null;


select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null




select DISTINCT DOXF.DXIXDA FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null;

select top 10 DOXF.DXINNR FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null;

--get indexes that contains value not in the right format (yyyy-MM-dd)
select distinct DOXF.DXIXID  FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND TRY_PARSE(DOXF.DXIXDA as date) is null;


--get DATE values not containing / or -
select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
WHERE (IDXT.IXXMLK = 'DATE')
AND NOT ((DOXF.DXIXDA like '%/%')
OR (DOXF.DXIXDA like '%-%'))