Problème de conversion d'une requête Oracle vers SQL serveur
:cry:
Bonjour,
J'ai l'erreur suivante :
Msg 241, Niveau 16, État 1, Ligne 1
Conversion failed when converting date and/or time from character string.
Requête SQL Server après transformation :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| SELECT CAST(
CASE CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE)
WHEN 0 THEN F.SEC_NO_EQUIPE
ELSE SUBSTRING(A.HORT_LEGENDE, CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE) + 13, 7)
END AS VARCHAR) + '|' + CAST(B.REP_CODE_TELEPHONIQUE AS VARCHAR) + '|' + CAST(B.REP_NOM AS VARCHAR) + '|' + CAST(B.REP_PRENOM AS VARCHAR) + '|' + CAST(CONVERT(VARCHAR(23), A.HORT_DATE) AS VARCHAR) + '|' + CAST(A.HORT_DUREE_ACTIVITES_MIN AS VARCHAR) + '|' + CAST(
CASE CHARINDEX('ENTRAIDE', A.HORT_LEGENDE)
WHEN 21 THEN ((SUBSTRING(A.HORT_LEGENDE, 1, 20) + 'En service ') + SUBSTRING(A.HORT_LEGENDE, 21, LEN(A.HORT_LEGENDE)))
ELSE A.HORT_LEGENDE
END AS VARCHAR) + '|' + CAST(B.REP_TELEPHONE_01 AS VARCHAR)
FROM CALTYPESCONTRAINTE D RIGHT OUTER JOIN CALCONTRAINTESXPREPOSE E ON D.RES_SEQUENCE = E.REP_RES_XRF_RES_SEQUENCE RIGHT OUTER JOIN CALHORAIRESXJOURXPREPOSE A ON E.REP_RES_SEQUENCE = A.HORT_REF_RES_PRO_SEQUENCE ,
CALPREPOSES B,
CALPREPOSESXEQUIPE C,
CALEQUIPES F
WHERE A.HORT_DATE >= CONVERT(DATETIME, '&1', 103)
AND A.HORT_DATE <= CONVERT(DATETIME, '&2', 103)
AND F.SEC_SEQUENCE = C.REP_SEC_XRF_SEC_SEQUENCE
AND B.REP_SEQUENCE = C.REP_SEC_XRF_REP_SEQUENCE
AND B.REP_SEQUENCE = A.HORT_XRF_REP_SEQUENCE
AND A.HORT_LEGENDE NOT LIKE '%Non disponible%'
AND F.SEC_NO_EQUIPE NOT LIKE '5%'
AND F.SEC_NO_EQUIPE NOT LIKE 'W%'
AND F.SEC_NO_EQUIPE NOT LIKE '0100%'
AND B.REP_TELEPHONE_01 IS NOT NULL
ORDER BY A.HORT_DATE,
C.REP_SEC_REF_NO_EQUIPE,
B.REP_CODE_TELEPHONIQUE,
A.HORT_HEURE_DEBUT |
Requête Oracle d'origine :
Code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| SELECT F.SEC_NO_EQUIPE||'|'||
B.REP_CODE_TELEPHONIQUE||'|'||
B.REP_NOM||'|'||
B.REP_PRENOM||'|'||
TO_CHAR(A.HORT_DATE, 'YYYYMMDD')||'|'||
A.HORT_DUREE_ACTIVITES_MIN||'|'||
A.HORT_LEGENDE||'|'||
D.RES_CODE||'|'||
B.REP_NO_EMPLOYE||'|'||
B.REP_TELEPHONE_01
FROM CALHORAIRESXJOURXPREPOSE A,
CALPREPOSES B,
CALPREPOSESXEQUIPE C,
CALTYPESCONTRAINTE D,
CALCONTRAINTESXPREPOSE E,
CALEQUIPES F
WHERE A.HORT_DATE>=TO_DATE ('&1','DD/MM/YYYY') AND
A.HORT_DATE<=TO_DATE ('&2','DD/MM/YYYY') AND
F.SEC_SEQUENCE=C.REP_SEC_XRF_SEC_SEQUENCE AND
B.REP_SEQUENCE=C.REP_SEC_XRF_REP_SEQUENCE AND
B.REP_SEQUENCE=A.HORT_XRF_REP_SEQUENCE AND
A.HORT_LEGENDE NOT LIKE '%Non disponible%' AND
D.RES_SEQUENCE(+) = E.REP_RES_XRF_RES_SEQUENCE AND
E.REP_RES_SEQUENCE(+) = A.HORT_REF_RES_PRO_SEQUENCE
ORDER BY A.HORT_DATE,
C.REP_SEC_REF_NO_EQUIPE,
B.REP_CODE_TELEPHONIQUE,
A.HORT_HEURE_DEBUT; |
Merci d'avance pour votre aide.
Cordialement