ms-sql 날짜변환 함수로 인한 문제

Question

안녕하십니까..주말에 혼자 SQL 작업을 하다가 이상한 문제가 있어서 여쭤보려고 이렇게 글을 씁니다.현재 기존에 있던 테이블의 데이터를 연도별로 각각 다른 테이블에 복사(추가되는 컬럼 있음)를 하려고작업 중입니다.그런데 작업 중 이상하게 날짜 변환하는 부분에서 잘 풀리지 않습니다.아래 소스에서 &#64;START_DATE와 &#64;END_DATE를YYYY-MM-DD까지만 가지고 (SELECT CONVERT(VARCHAR(10),&#64;START_DATE, 23)으로 변환해서 실행을하면 잘 복사가 됩니다..그런데 YYYY-MM-DD HH:MM:SS까지 가져오기 위해 (SELECT CONVERT(VARCHAR(19),&#64;START_DATE, 120)으로 고쳐서 실행을 하면정상적으로 데이터를 가지고 오지 못 합니다.어떤 문제인지 찾고 찾다가 못 찾겠어서 이렇게 여쭤봅니다.아래 소스를 첨부했습니다. 참조하여 도움을 주시면 정말 감사하겠습니다. *소스참조* DECLARE &#64;START_CB VARCHAR(20) DECLARE &#64;END_CB VARCHAR(20) SET &#64;START_CB &#61; '1' SET &#64;END_CB &#61; CAST(( CAST(&#64;START_CB as int) &#43; 1) as CHAR(20)) WHILE &#64;START_CB <&#61; &#64;END_CB BEGIN DECLARE &#64;Table_ID VARCHAR(20) DECLARE &#64;userSql VARCHAR(1000) DECLARE &#64;START_DATE DATETIME DECLARE &#64;END_DATE DATETIME DECLARE &#64;DATE_STRING VARCHAR(40) &#61; NULL SET &#64;Table_ID &#61; 'CB'&#43; REPLICATE(0, 5 -LEN(&#64;START_CB)) &#43; &#64;START_CB SET &#64;START_DATE&#61;'2017-01-01 00:00:00' SET &#64;END_DATE&#61;'2017-01-02 23:59:59' WHILE &#64;START_DATE <&#61; &#64;END_DATE BEGIN SET &#64;DATE_STRING &#61; (SELECT CONVERT(VARCHAR(19),&#64;START_DATE, 120)) DECLARE &#64;Cus_No INT DECLARE &#64;shDate VARCHAR(40) set &#64;shDate &#61; &#64;DATE_STRING begin if OBJECT_ID('tempdb..#LoadTrends') is not null drop table #LoadTrends CREATE TABLE #LoadTrends ( Cus_No int , Log_Date CHAR(40) , hh int , Inv_SeqNum int , S_Volt float , S_Current float , S_Power float , U_Volt_R float , I_Curr_U float , I_Power float , U_Hz float , I_Efficiency float , S_Env_Inclined float , S_Env_Plane float , S_Env_Out_Heat float , S_Env_Cell_Heat float , S_Env_Wind float , I_Minute float , created datetime) set &#64;userSql &#61; 'insert into #LoadTrends select Cus_No, CONVERT(CHAR(19), Log_Date, 120) as Log_Date, datepart(HH, Log_Date) hh, Inv_SeqNum, ' set &#64;userSql &#43;&#61; ' S_Volt, S_Current, S_Power, U_Volt_R, I_Curr_U, I_Power, U_Hz, I_Efficiency, ' set &#64;userSql &#43;&#61; ' S_Env_Inclined, S_Env_Plane, S_Env_Out_Heat, S_Env_Cell_Heat, ' set &#64;userSql &#43;&#61; ' S_Env_Wind, I_Minute, GETDATE() as created ' set &#64;userSql &#43;&#61; ' from '&#43;&#64;Table_ID&#43;'_RTData ' set &#64;userSql &#43;&#61; ' where convert(char(19), Log_Date, 120) &#61; ''' &#43; &#64;shDate &#43; '''' exec (&#64;userSql); WITH CTE_Daily( Cus_No, Log_Date, Inv_SeqNum, S_Volt, S_Current, S_Power, U_Volt_R, I_Curr_U, I_Power, U_Hz, I_Efficiency, S_Env_Inclined, S_Env_Plane, S_Env_Out_Heat, S_Env_Cell_Heat, S_Env_Wind, I_Minute, created) AS (Select Cus_No, Log_Date, inv_seqnum, s_volt, s_current, s_power, u_volt_r, i_curr_u, i_power, u_hz, i_efficiency, s_env_inclined, s_env_plane, s_env_out_heat, s_env_cell_heat, s_env_wind, i_minute, created From #LoadTrends) INSERT INTO [solarlink_arrange].[dbo].[Trends_2017]( Cus_No, CB_ID, Log_Date, hh, inv_seqnum, s_volt, s_current, s_power, u_volt_r, i_curr_u, i_power, u_hz, i_efficiency, s_env_inclined, s_env_plane, s_env_out_heat, s_env_cell_heat, s_env_wind, i_minute, created) Select Cus_No as Cus_No, &#64;Table_ID as CB_ID, convert(varchar(19), &#64;shDate, 120) as report_day ,datepart(HH, Log_Date) as hh, Inv_SeqNum, s_volt as S_Volt, s_current as S_Current, S_Power as S_Power, u_volt_r as u_volt_r, i_curr_u as i_curr_u, I_Power as I_Power, U_Hz as U_Hz, I_Efficiency as I_Efficiency, S_Env_Inclined as S_Env_Inclined, S_Env_Plane as S_Env_Plane, S_Env_Out_Heat as S_Env_Out_Heat, S_Env_Cell_Heat as S_Env_Cell_Heat, S_Env_Wind as S_Env_Wind, I_Minute as I_Minute, created as created from CTE_Daily if OBJECT_ID('tempdb..#LoadTrends') is not null drop table #LoadTrends END SET &#64;START_DATE &#61; dateadd(d,1,&#64;START_DATE) END SET &#64;START_CB &#61; &#64;START_CB &#43; 1 END

ms-sql 날짜변환 함수로 인한 문제

0개의 답변

Q&A 목록