Convert number into long date

Question

There is a column in a SQL database (I'm working with SQL-SMT), the data looks like this:

For the first element, the purpose is to get something like: 21-10-2014 16:54:35 but I can't figure out how to do it, I already checked this post, but they use the '%' function, which according to SQL is incompatible with float and int. I sorted the code in this way:

select [Hora origen ] ,
       nHora= convert(time ,
                 right( '0' + convert(varchar, [Hora origen] / 10000 ) , 2 )
               + ':'
               + right( '0' + (([Hora origen]-([Hora origen] % 100))% 10000)/100 , 2 )
               + ':'
               + right( '0' + convert(varchar, [Hora origen] % 10000 ) , 2 ) 
               )
from Trx_tj$;

But still, it's not working

there is no number for the year, because all the dates are for the year 2014 — eyanquenb, Dec 18 '14 at 22:40

score 0 · Accepted Answer · answered Dec 18 '14 at 23:59

Here is one way to do it:

DECLARE @DatetimeStr VARCHAR(20)
SET @DatetimeStr = '1021165435'
--SET @DatetimeStr = '1021215439'
--SET @DatetimeStr = '1021215441'
--SET @DatetimeStr = '1021162126'
--SET @DatetimeStr = '1021162122'
--SET @DatetimeStr = '1021212135'
--SET @DatetimeStr = '1021162137'
--SET @DatetimeStr = '1021212136'
--SET @DatetimeStr = '1021213136'

SELECT
  DATEADD(SECOND, CAST(SUBSTRING(@DatetimeStr, 9, 2) AS INT),
          DATEADD(MINUTE, CAST(SUBSTRING(@DatetimeStr, 7, 2) AS INT),
                  DATEADD(HOUR, CAST(SUBSTRING(@DatetimeStr, 5, 2) AS INT),
                          DATEADD(DAY,
                                  CAST(SUBSTRING(@DatetimeStr, 3, 2) AS INT),
                                  DATEADD(month,
                                          CAST(LEFT(@DatetimeStr, 2) AS INT) - 1,
                                          CAST('20131231' AS DATETIME))))))

score 0 · Answer 2 · answered Dec 19 '14 at 00:58

Here is another way:

SAMPLE DATA

CREATE TABLE temp(
    dateString VARCHAR(20)
)
INSERT INTO temp VALUES
('1021165435'), ('1021215439'), ('1021215441'),
('1021162126'), ('1021162122'), ('1021212135'),
('1021162137'), ('1021212136'), ('1021213136');

SOLUTION

WITH CTE AS(
    SELECT
        dateString,
        [Month] = CAST(LEFT(datestring, 2) AS INT) - 1, 
        [Day] = CAST(SUBSTRING(datestring, 3, 2) AS INT)- 1,
        [Hour] = CAST(SUBSTRING(datestring, 5, 2) AS INT),
        [Minute] = CAST(SUBSTRING(datestring, 7, 2) AS INT),
        [Second] = CAST(SUBSTRING(datestring, 9, 2) AS INT)
    FROM temp
)
SELECT
    dateString,
    Formatted = 
        CONVERT(VARCHAR(10), DATEADD(D, [Day], DATEADD(M, [Month], '20140101')), 105) + ' ' +
        CONVERT(VARCHAR(8), DATEADD(SS, [Second], DATEADD(MI, [Minute], DATEADD(HH, [Hour], 0))), 114)
FROM cte

Convert number into long date

2 Answers2

SOLUTION