none
concatenate datetime in different column

    질문

  • Hi, 

    I am a beginner in sql, maybe it's an easy thing ...

    I have a table with the fields year, month, day, hour, minutes and seconds separeted (date type is float). I create a new column 'Date' as datetime and now I would like for each record put those values in the new field 'Date'. I have tried with update and cast but I did not get it.  I have a very large table so it should be automatic, for each record is compiled with data from registration.

    I put a small example of what I explain below. 

    Thank you very much for the help

    ID      Year     Month    Day        Hour     Minutes       Seconds                 newField Date

    1     2017     4        25      11       25            50          2017-04-25 11:25:50

    2     2017      4       25      9         50            24          2017-04-25 09:50:24

    3     2017      3        10     13        2              47         2017-03-10 13:02:47



    • 편집됨 pedro787 2018년 5월 17일 목요일 오후 5:38
    2018년 5월 17일 목요일 오후 5:11

모든 응답

  • Use DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

    like this

    SELECT DATETIMEFROMPARTS ( 2017, 4, 25, 11, 25, 50, 0)

    • 답변으로 제안됨 Vinay Manikanda 2018년 5월 17일 목요일 오후 5:27
    2018년 5월 17일 목요일 오후 5:13
    중재자
  • Hi,

    Use below code for concatenation and handle float data types. Replace the value with actual column type in below code.

    SELECT     
    CONCAT(CAST(2017 AS FLOAT), '-',    RIGHT('00'+cast(CAST(4 AS FLOAT) AS VARCHAR(5)),2) ,'-',       RIGHT('00'+cast(CAST(25 AS FLOAT) AS VARCHAR(5)),2) , ' ',  RIGHT('00'+cast(CAST(11 AS FLOAT) AS VARCHAR(5)),2)  , ':',     RIGHT('00'+cast(CAST(25  AS FLOAT) AS VARCHAR(5)),2) , ':',         RIGHT('00'+cast(CAST(50 AS FLOAT) AS VARCHAR(5)),2)) AS [NewField Date]

    UNION

    SELECT     
    CONCAT(CAST(2017 AS FLOAT), '-',    RIGHT('00'+cast(CAST(4 AS FLOAT) AS VARCHAR(5)),2) ,'-',       RIGHT('00'+cast(CAST(25 AS FLOAT) AS VARCHAR(5)),2) , ' ',  RIGHT('00'+cast(CAST(9 AS FLOAT) AS VARCHAR(5)),2)  , ':',     RIGHT('00'+cast(CAST(50  AS FLOAT) AS VARCHAR(5)),2) , ':',         RIGHT('00'+cast(CAST(24 AS FLOAT) AS VARCHAR(5)),2)) 

    UNION

    SELECT     
    CONCAT(CAST(2017 AS FLOAT), '-',    RIGHT('00'+cast(CAST(3 AS FLOAT) AS VARCHAR(5)),2) ,'-',       RIGHT('00'+cast(CAST(10 AS FLOAT) AS VARCHAR(5)),2) , ' ',  RIGHT('00'+cast(CAST(13 AS FLOAT) AS VARCHAR(5)),2)  , ':',     RIGHT('00'+cast(CAST(2  AS FLOAT) AS VARCHAR(5)),2) , ':',         RIGHT('00'+cast(CAST(47 AS FLOAT) AS VARCHAR(5)),2)) 

    --Output:

    2017-03-10 13:02:47
    2017-04-25 09:50:24
    2017-04-25 11:25:50

    Hope this helps!

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as Answered if this helps


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!

    2018년 5월 17일 목요일 오후 5:28
  •  I have a very large table so it should be automatic, for each record is compiled with data from registration.

    I put a small example of what I explain below. 

    Thank you very much for the help

    ID      Year     Month    Day        Hour     Minutes       Seconds                 newField Date

    1     2017     4        25      11       25            50          2017-04-25 11:25:50

    2     2017      4       25      9         50            24          2017-04-25 09:50:24

    3     2017      3        10     13        2              47         2017-03-10 13:02:47


    Hi pedro787,

    The function DATETIMEFROMPARTS is the best choice, which is provided by Jingyang.

    Besides, based on large amount of the data in the table, you could update the table records in batch by referring to the script code below.

    create table DateTable
    (
     ID float,
     [Year] float,
     [Month] float,
     [Day] float,
     [Hour] float,
     [Minutes] float,
     [Seconds] float,
     [newField date] datetime
     )
    
     insert into DateTable values
     (1,2017,4,25,11,25,50,null),
     (2,2017,4,25,9,50,24,null),
     (3,2017,3,10,13,2,47,null)
    
    
     ----
    
     DECLARE @Rows INT,
            @BatchSize INT; -- keep below 5000 to be safe
    
    SET @BatchSize = 2000;
    
    SET @Rows = @BatchSize; -- initialize just to enter the loop
    
      
      WHILE (@Rows = @BatchSize)
      BEGIN
          UPDATE TOP (@BatchSize) DateTable
          SET    [newField date] = DATETIMEFROMPARTS([Year],[Month],[Day],[Hour],[Minutes],[Seconds],0)
          WHERE [newField date] is null
    
          SET @Rows = @@ROWCOUNT;
      END;
    
      select * from DateTable

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 18일 금요일 오전 3:18