How to concatentate date and time fields and convert to datetime stamp

Question

Hello Community,

I am trying to concatentate date and time values which are currently created as Integer type fields and place the results into another field in the format YYYY-MM-dd hh:mm:ss

So, I have the following table

I would like concatenate LAST_MODE_DATE and LAST_MOD_TIME and place the results in LAST_MODIFICATION_DT, so that it appears as follows:

The data for the above is as follows:

CREATE TABLE #tmpTable (  
    LAST_MOD_DATE int,  
    LAST_MOD_TIME int,  
    LAST_MODIFICATION_USER varchar(50),  
    LAST_MODIFICATION_DT varchar(255))  
  
INSERT #tmpTable VALUES  
(NULL,NULL,NULL,NULL),  
(20181214,142254,'14843',NULL),  
(20210129,193402,'AHARDY4',NULL),  
(NULL,NULL,NULL,NULL)  
  
SELECT * FROM #tmpTable  
  

Thanks

Answer 1

Try these acrobatics too:

update #tmpTable
set LAST_MODIFICATION_DT = format(LAST_MOD_DATE, '0000"-"00"-"00" "') + format(LAST_MOD_TIME, '00":"00":"00')

Answer 2

Why varchar(255) for the LAST_MODIFICATION_DT column? What's wrong with datetime2(0)? Storing date/time values in strings is a lousy idea. Sooner or later you will be looking at 2023-02-29 25:12:76 or something equally silly.

Anyway, here is a query for you:

UPDATE #tmpTable
SET    LAST_MODIFICATION_DT = convert(varchar(255),
                                 convert(datetime2(0), 
                                   convert(char(8), LAST_MOD_DATE) + ' ' +
                                   convert(char(2), LAST_MOD_TIME / 10000) + ':' +
                                   convert(char(2), (LAST_MOD_TIME / 100) % 100) + ':' +
                                   convert(char(2), LAST_MOD_TIME % 100)
                                ),
                            121)

Answer 3

ErlandSommarskog,

Thanks for getting in touch.

The "LAST_MODIFICATION_DT " will be datetime2, not VARCHAR(255) - that is my error.

However, I was hoping you could just take concatentate the two fields and place the results in the LAST_MODIFICATION_DT field.

The solution you provided seems very long.