代码之家  ›  专栏  ›  技术社区  ›  Arun Kumar

从字符串转换日期和/或时间时转换失败(尝试保存XML数据时)

  •  0
  • Arun Kumar  · 技术社区  · 10 年前

    我有一个XML文件数据,如下所示。

    '<NewDataSet>
      <tblVCWS_Schedule>
        <PKWSSchedule>1</PKWSSchedule>    
        <ScheduleTime>PT7H</ScheduleTime>    
      </tblVCWS_Schedule>
      <tblVCWS_Schedule>
        <PKWSSchedule>2</PKWSSchedule>   
        <ScheduleTime>PT13H30M</ScheduleTime>    
      </tblVCWS_Schedule>
      <tblVCWS_Schedule>
        <PKWSSchedule>3</PKWSSchedule>    
        <ScheduleTime>PT13H30M</ScheduleTime>    
      </tblVCWS_Schedule> 
    </NewDataSet>'
    

    ScheduleTime字段是SQL中的时间(7)数据类型字段。

    但当我试图将数据保存到临时表中时

    “从字符串转换日期和/或时间时转换失败”

     DECLARE @PreAdviceSubmissions  varchar(max) 
    
        set @PreAdviceSubmissions=
        '<NewDataSet>
          <tblVCWS_Schedule>
            <PKWSSchedule>1</PKWSSchedule>    
            <ScheduleTime>PT7H</ScheduleTime>    
          </tblVCWS_Schedule>
          <tblVCWS_Schedule>
            <PKWSSchedule>2</PKWSSchedule>   
            <ScheduleTime>PT13H30M</ScheduleTime>    
          </tblVCWS_Schedule>
          <tblVCWS_Schedule>
            <PKWSSchedule>3</PKWSSchedule>    
            <ScheduleTime>PT13H30M</ScheduleTime>    
          </tblVCWS_Schedule> 
        </NewDataSet>'
    
        DECLARE @hDoc int
    
            CREATE TABLE #PreAdviceSubmissions
            (   
               PKWSSchedule bigint,
               ScheduleTime Time(7)
            )
    
            EXECUTE sp_xml_preparedocument @hDoc OUTPUT, @PreAdviceSubmissions
    
            INSERT INTO #PreAdviceSubmissions 
            (
               PKWSSchedule,
               ScheduleTime
            )
            SELECT 
               PKWSSchedule ,
               CAST(ScheduleTime AS TIME(7)) 
            FROM OPENXML(@hDoc,'/NewDataSet/tblVCWS_Schedule', 2)
            WITH 
            (
                PKWSSchedule  bigint 'PKWSSchedule' ,   
                ScheduleTime  Time(7) 'ScheduleTime'          
            )
    
            EXECUTE sp_xml_removedocument @hDoc
    
            select * from #PreAdviceSubmissions
    

    有人能帮忙解决这个问题吗?请注意,我不能将数据类型Time(7)更改为任何其他数据类型。

    2 回复  |  直到 10 年前
        1
  •  0
  •   Hiral Nayak    10 年前

    试试这个

        INSERT INTO #PreAdviceSubmissions 
        (
           PKWSSchedule,
           ScheduleTime
        )
        SELECT 
           PKWSSchedule ,
           CASE WHEN (SELECT CHARINDEX('%M%',ScheduleTime)) > 0 THEN 
           CONVERT(time(7),REPLACE(REPLACE(REPLACE(ScheduleTime,'PT',''),'H',':'),'M',''))
           ELSE
           CONVERT(time(7),REPLACE(REPLACE(ScheduleTime,'PT',''),'H',':00')) END
        FROM OPENXML(@hDoc,'/NewDataSet/tblVCWS_Schedule', 2)
        WITH 
        (
            PKWSSchedule  bigint 'PKWSSchedule' ,   
            ScheduleTime  Time(7) 'ScheduleTime'          
        )
    
        2
  •  -1
  •   Dinesh Reddy Alla    10 年前

    DECLARE @TIME VARCHAR(50) = 'PT13H30M'
    
    

    IF (SELECT PATINDEX('%M%','@TIME') > 0) BEGIN SELECT CONVERT(TIME,REPLACE(REPLACE(REPLACE(@TIME,'PT',''),'H',':'),'M','')) END ELSE BEGIN SELECT CONVERT(TIME,REPLACE(REPLACE(@TIME,'PT',''),'H',':00')) END