데이터베이스 질문과 답변
dbo.UDF_DATETIME_TO_VARCHAR8() 가 MSSQL 에서는 안되고 있습니다
0
2
375
by 이혜선 [SQL Query] MSSQL [2022.01.05 03:19:21]
MSSQL 를 사용하고 있습니다.
dbo.UDF_DATETIME_TO_VARCHAR8() 이 함수가 MSSQL 에서는 안되고 있습니다.
아래는 문제의 소스입니다.
dbo.UDF_DATETIME_TO_VARCHAR8() 어떻게 해결해야 할까요?
MSSQL 에서 DBO.Get_DateTime() 으로는 dbo.UDF_DATETIME_TO_VARCHAR8() 대체가 되지 않고 있습니다
WITH PT AS ( SELECT 1 AS LV, dbo.UDF_VARCHAR14_TO_DATETIME(@WORK_DATE) AS DT UNION ALL SELECT LV+1, DT+1 AS DT FROM PT WHERE LV < 11 ) SELECT A.PLANT_CODE, A.LINE_CODE, @WORK_DATE AS PLAN_DATE, CONVERT(INT, ROUND(((LEN(T1)-LEN(REPLACE(T1,'1',''))) /60.0) * B.UPH, 0)) AS T1_CAPA, CONVERT(INT, ROUND(((LEN(T2)-LEN(REPLACE(T2,'1','')))/60.0) * B.UPH, 0)) AS T2_CAPA, CONVERT(INT, ROUND(((LEN(T3)-LEN(REPLACE(T3,'1','')))/60.0) * B.UPH, 0)) AS T3_CAPA, CONVERT(INT, ROUND(((LEN(T4)-LEN(REPLACE(T4,'1','')))/60.0) * B.UPH, 0)) AS T4_CAPA, CONVERT(INT, ROUND(((LEN(T5)-LEN(REPLACE(T5,'1','')))/60.0) * B.UPH, 0)) AS T5_CAPA, CONVERT(INT, ROUND(((LEN(T6)-LEN(REPLACE(T6,'1','')))/60.0) * B.UPH, 0)) AS T6_CAPA, CONVERT(INT, ROUND(((LEN(T7)-LEN(REPLACE(T7,'1','')))/60.0) * B.UPH, 0)) AS T7_CAPA, CONVERT(INT, ROUND(((LEN(T8)-LEN(REPLACE(T8,'1','')))/60.0) * B.UPH, 0)) AS T8_CAPA, CONVERT(INT, ROUND(((LEN(T9)-LEN(REPLACE(T9,'1','')))/60.0) * B.UPH, 0)) AS T9_CAPA, CONVERT(INT, ROUND(((LEN(T10)-LEN(REPLACE(T10,'1',''))) /60.0) * B.UPH, 0)) AS T10_CAPA, CONVERT(INT, ROUND(((LEN(T11)-LEN(REPLACE(T11,'1',''))) /60.0) * B.UPH, 0)) AS T11_CAPA, /* D1 T1 */ CONVERT(INT, ROUND(((LEN(T12)-LEN(REPLACE(T12,'1',''))) /60.0) * B.UPH, 0)) AS T12_CAPA, /* D1 T2 */ CONVERT(INT, ROUND(((LEN(T13)-LEN(REPLACE(T13,'1',''))) /60.0) * B.UPH, 0)) AS T13_CAPA, /* D1 T3 */ CONVERT(INT, ROUND(((LEN(T14)-LEN(REPLACE(T14,'1',''))) /60.0) * B.UPH, 0)) AS T14_CAPA, /* D1 T4 */ CONVERT(INT, ROUND(((LEN(T15)-LEN(REPLACE(T15,'1',''))) /60.0) * B.UPH, 0)) AS T15_CAPA, /* D1 T5 */ CONVERT(INT, ROUND(((LEN(T16)-LEN(REPLACE(T16,'1',''))) /60.0) * B.UPH, 0)) AS T16_CAPA, /* D1 T6 */ CONVERT(INT, ROUND(((LEN(T17)-LEN(REPLACE(T17,'1',''))) /60.0) * B.UPH, 0)) AS T17_CAPA, /* D1 T7 */ CONVERT(INT, ROUND(((LEN(T18)-LEN(REPLACE(T18,'1',''))) /60.0) * B.UPH, 0)) AS T18_CAPA, /* D1 T8 */ CONVERT(INT, ROUND(((LEN(T19)-LEN(REPLACE(T19,'1',''))) /60.0) * B.UPH, 0)) AS T19_CAPA, /* D1 T9 */ CONVERT(INT, ROUND(((LEN(T20)-LEN(REPLACE(T20,'1',''))) /60.0) * B.UPH, 0)) AS T20_CAPA, /* D1 T10 */ CONVERT(INT, ROUND(((LEN(T21)-LEN(REPLACE(T21,'1',''))) /60.0) * B.UPH, 0)) AS T21_CAPA, /* D2 T1 */ CONVERT(INT, ROUND(((LEN(T22)-LEN(REPLACE(T22,'1',''))) /60.0) * B.UPH, 0)) AS T22_CAPA, /* D2 T2 */ CONVERT(INT, ROUND(((LEN(T23)-LEN(REPLACE(T23,'1',''))) /60.0) * B.UPH, 0)) AS T23_CAPA, /* D2 T3 */ CONVERT(INT, ROUND(((LEN(T24)-LEN(REPLACE(T24,'1',''))) /60.0) * B.UPH, 0)) AS T24_CAPA, /* D2 T4 */ CONVERT(INT, ROUND(((LEN(T25)-LEN(REPLACE(T25,'1',''))) /60.0) * B.UPH, 0)) AS T25_CAPA, /* D2 T5 */ CONVERT(INT, ROUND(((LEN(T26)-LEN(REPLACE(T26,'1',''))) /60.0) * B.UPH, 0)) AS T26_CAPA, /* D2 T6 */ CONVERT(INT, ROUND(((LEN(T27)-LEN(REPLACE(T27,'1',''))) /60.0) * B.UPH, 0)) AS T27_CAPA, /* D2 T7 */ CONVERT(INT, ROUND(((LEN(T28)-LEN(REPLACE(T28,'1',''))) /60.0) * B.UPH, 0)) AS T28_CAPA, /* D2 T8 */ CONVERT(INT, ROUND(((LEN(T29)-LEN(REPLACE(T29,'1',''))) /60.0) * B.UPH, 0)) AS T29_CAPA, /* D2 T9 */ CONVERT(INT, ROUND(((LEN(T30)-LEN(REPLACE(T30,'1',''))) /60.0) * B.UPH, 0)) AS T30_CAPA, /* D2 T10 */ CONVERT(INT, ROUND(((LEN(T31)-LEN(REPLACE(T31,'1',''))) /60.0) * B.UPH, 0)) AS T31_CAPA, /* D3 T1 */ CONVERT(INT, ROUND(((LEN(T32)-LEN(REPLACE(T32,'1',''))) /60.0) * B.UPH, 0)) AS T32_CAPA, /* D3 T2 */ CONVERT(INT, ROUND(((LEN(T33)-LEN(REPLACE(T33,'1',''))) /60.0) * B.UPH, 0)) AS T33_CAPA, /* D3 T3 */ CONVERT(INT, ROUND(((LEN(T34)-LEN(REPLACE(T34,'1',''))) /60.0) * B.UPH, 0)) AS T34_CAPA, /* D3 T4 */ CONVERT(INT, ROUND(((LEN(T35)-LEN(REPLACE(T35,'1',''))) /60.0) * B.UPH, 0)) AS T35_CAPA, /* D3 T5 */ CONVERT(INT, ROUND(((LEN(T36)-LEN(REPLACE(T36,'1',''))) /60.0) * B.UPH, 0)) AS T36_CAPA, /* D3 T6 */ CONVERT(INT, ROUND(((LEN(T37)-LEN(REPLACE(T37,'1',''))) /60.0) * B.UPH, 0)) AS T37_CAPA, /* D3 T7 */ CONVERT(INT, ROUND(((LEN(T38)-LEN(REPLACE(T38,'1',''))) /60.0) * B.UPH, 0)) AS T38_CAPA, /* D3 T8 */ CONVERT(INT, ROUND(((LEN(T39)-LEN(REPLACE(T39,'1',''))) /60.0) * B.UPH, 0)) AS T39_CAPA, /* D3 T9 */ CONVERT(INT, ROUND(((LEN(T40)-LEN(REPLACE(T40,'1',''))) /60.0) * B.UPH, 0)) AS T40_CAPA, /* D3 T10 */ CONVERT(INT, ROUND(((LEN(T41)-LEN(REPLACE(T41,'1',''))) /60.0) * B.UPH, 0)) AS T41_CAPA, /* D4 T1 */ CONVERT(INT, ROUND(((LEN(T42)-LEN(REPLACE(T42,'1',''))) /60.0) * B.UPH, 0)) AS T42_CAPA, /* D4 T2 */ CONVERT(INT, ROUND(((LEN(T43)-LEN(REPLACE(T43,'1',''))) /60.0) * B.UPH, 0)) AS T43_CAPA, /* D4 T3 */ CONVERT(INT, ROUND(((LEN(T44)-LEN(REPLACE(T44,'1',''))) /60.0) * B.UPH, 0)) AS T44_CAPA, /* D4 T4 */ CONVERT(INT, ROUND(((LEN(T45)-LEN(REPLACE(T45,'1',''))) /60.0) * B.UPH, 0)) AS T45_CAPA, /* D4 T5 */ CONVERT(INT, ROUND(((LEN(T46)-LEN(REPLACE(T46,'1',''))) /60.0) * B.UPH, 0)) AS T46_CAPA, /* D4 T6 */ CONVERT(INT, ROUND(((LEN(T47)-LEN(REPLACE(T47,'1',''))) /60.0) * B.UPH, 0)) AS T47_CAPA, /* D4 T7 */ CONVERT(INT, ROUND(((LEN(T48)-LEN(REPLACE(T48,'1',''))) /60.0) * B.UPH, 0)) AS T48_CAPA, /* D4 T8 */ CONVERT(INT, ROUND(((LEN(T49)-LEN(REPLACE(T49,'1',''))) /60.0) * B.UPH, 0)) AS T49_CAPA, /* D4 T9 */ CONVERT(INT, ROUND(((LEN(T50)-LEN(REPLACE(T50,'1',''))) /60.0) * B.UPH, 0)) AS T50_CAPA, /* D4 T10 */ CONVERT(INT, ROUND(((LEN(D5_TT)-LEN(REPLACE(D5_TT,'1',''))) /60.0) * B.UPH, 0)) AS T51_CAPA, /* D5 TT */ CONVERT(INT, ROUND(((LEN(D6_TT)-LEN(REPLACE(D6_TT,'1',''))) /60.0) * B.UPH, 0)) AS T52_CAPA, /* D6 TT */ CONVERT(INT, ROUND(((LEN(D7_TT)-LEN(REPLACE(D7_TT,'1',''))) /60.0) * B.UPH, 0)) AS T53_CAPA, /* D7 TT */ CONVERT(INT, ROUND(((LEN(D8_TT)-LEN(REPLACE(D8_TT,'1',''))) /60.0) * B.UPH, 0)) AS T54_CAPA, /* D8 TT */ CONVERT(INT, ROUND(((LEN(D9_TT)-LEN(REPLACE(D9_TT,'1',''))) /60.0) * B.UPH, 0)) AS T55_CAPA /* D9 TT */ FROM ( SELECT PLANT_CODE, LINE_CODE, SUBSTRING([1],1, 120) AS T1, /* 08~10 */ SUBSTRING([1],121, 120) AS T2, /*10~12 */ SUBSTRING([1],301, 120) AS T3, /* 13~15 */ SUBSTRING([1],421, 120) AS T4, /* 15~17 */ SUBSTRING([1],541, 120) AS T5, /* 17~19 */ SUBSTRING([1],721, 120) AS T6, /* 20~ 22 */ SUBSTRING([1],841, 120) AS T7, /* 22~24 */ SUBSTRING([1],1021, 120) AS T8, /* 01~03 */ SUBSTRING([1],1141, 120) AS T9, /* 03~05 */ SUBSTRING([1],1261, 120) AS T10, /* 05~07 */ /* D+1 */ SUBSTRING([2],1, 120) AS T11, /* 08~10 */ SUBSTRING([2],121, 120) AS T12, /*10~12 */ SUBSTRING([2],301, 120) AS T13, /* 13~15 */ SUBSTRING([2],421, 120) AS T14, /* 15~17 */ SUBSTRING([2],541, 120) AS T15, /* 17~19 */ SUBSTRING([2],721, 120) AS T16, /* 20~ 22 */ SUBSTRING([2],841, 120) AS T17, /* 22~24 */ SUBSTRING([2],1021, 120) AS T18, /* 01~03 */ SUBSTRING([2],1141, 120) AS T19, /* 03~05 */ SUBSTRING([2],1261, 120) AS T20, /* 05~07 */ /* D+2 */ SUBSTRING([3],1, 120) AS T21, /* 08~10 */ SUBSTRING([3],121, 120) AS T22, /*10~12 */ SUBSTRING([3],301, 120) AS T23, /* 13~15 */ SUBSTRING([3],421, 120) AS T24, /* 15~17 */ SUBSTRING([3],541, 120) AS T25, /* 17~19 */ SUBSTRING([3],721, 120) AS T26, /* 20~ 22 */ SUBSTRING([3],841, 120) AS T27, /* 22~24 */ SUBSTRING([3],1021, 120) AS T28, /* 01~03 */ SUBSTRING([3],1141, 120) AS T29, /* 03~05 */ SUBSTRING([3],1261, 120) AS T30, /* 05~07 */ /* D+3 */ SUBSTRING([4],1, 120) AS T31, /* 08~10 */ SUBSTRING([4],121, 120) AS T32, /*10~12 */ SUBSTRING([4],301, 120) AS T33, /* 13~15 */ SUBSTRING([4],421, 120) AS T34, /* 15~17 */ SUBSTRING([4],541, 120) AS T35, /* 17~19 */ SUBSTRING([4],721, 120) AS T36, /* 20~ 22 */ SUBSTRING([4],841, 120) AS T37, /* 22~24 */ SUBSTRING([4],1021, 120) AS T38, /* 01~03 */ SUBSTRING([4],1141, 120) AS T39, /* 03~05 */ SUBSTRING([4],1261, 120) AS T40, /* 05~07 */ /* D+4 */ SUBSTRING([5],1, 120) AS T41, /* 08~10 */ SUBSTRING([5],121, 120) AS T42, /*10~12 */ SUBSTRING([5],301, 120) AS T43, /* 13~15 */ SUBSTRING([5],421, 120) AS T44, /* 15~17 */ SUBSTRING([5],541, 120) AS T45, /* 17~19 */ SUBSTRING([5],721, 120) AS T46, /* 20~ 22 */ SUBSTRING([5],841, 120) AS T47, /* 22~24 */ SUBSTRING([5],1021, 120) AS T48, /* 01~03 */ SUBSTRING([5],1141, 120) AS T49, /* 03~05 */ SUBSTRING([5],1261, 120) AS T50, /* 05~07 */ [6] AS D5_TT, /* D+5 DAY */ [7] AS D6_TT, /* D+6 DAY */ [8] AS D7_TT, /* D+7 DAY */ [9] AS D8_TT, /* D+8 DAY */ [10] AS D9_TT /* D+9 DAY */ FROM ( SELECT A.LV, ISNULL(B.PLANT_CODE, @PLANT_CODE) AS PLANT_CODE, ISNULL(B.LINE_CODE, @LINE_CODE) AS LINE_CODE, ISNULL(B.PATTERN, '00000000') AS PATTERN FROM ( SELECT LV, dbo.UDF_DATETIME_TO_VARCHAR8(DT) AS WORK_DATE FROM PT ) A LEFT OUTER JOIN ( SELECT * FROM ST_PATTERN WITH (NOLOCK) WHERE PLANT_CODE = @PLANT_CODE AND LINE_CODE = @LINE_CODE ) B ON A.WORK_DATE = B.WORK_DATE ) A PIVOT ( MAX(PATTERN) FOR LV IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]) ) AS PVT ) A INNER JOIN ( SELECT PLANT_CODE, KEY_1 AS LINE_CODE, DATA_1 AS UPH FROM MA_CODE_DATA WITH (NOLOCK) WHERE CODE_TABLE_NAME = 'CM_UPH' ) B ON A.PLANT_CODE = B.PLANT_CODE AND A.LINE_CODE = B.LINE_CODE
by 우주민
[2022.01.05 09:25:46]
일반적으로 사용되는 함수 명은 아닌듯 한데요.
아마 사용자 함수가 아닐까 싶습니다.
명칭으로 유추해볼때 날짜 형식의 데이터를 8자리 varchar 로 변경하는 함수일거 같네요.
날짜 형식으로 정확하게 내려진다면 to_char 함수를 사용하시는것도 생각해볼 수 있을거 같습니다.
by 마농
[2022.01.05 09:48:22]
UDF 로 시작하는 걸로 봐서는 사용자 함수 (User Define Function) 로 보입니다.
DATETIME_TO_VARCHAR8 로 봐서는 날짜 타입을 문자 8자리로 바꾸는 함수인 듯 하네요.
UDF_VARCHAR14_TO_DATETIME 는 14자리 문자를 날짜형으로 바꾸는 함수인 듯 하네요.
CONVERT 를 이용할 수 있는데. 14자리 문자를 날짜로 바꾸는건 가공이 좀 필요할 듯 하네요.
우선 8자리만 알려드릴께요.
- 날짜를 문자로 변경 , CONVERT(VARCHAR, getdate(), 112)
- 문자를 날짜로 변경 , CONVERT(DATETIME, '20220105', 112)
) 버튼을 클릭하여 작성 하시면 됩니다.