locked
Convert decimal to date time RRS feed

  • Question

  • Hi, How to convert a decimal value to date time

    create table #T (validto decimal)
    insert into #T (validto)
    values (20190824223234),
    (20190826224244),
    (20190825223739),
    (20190820220906),
    (20190821221254),
    (20180714005825),
    (20190823222907),
    (20190822221834),
    (20190827224611)


    Royal Thomas

    Thursday, August 29, 2019 9:26 PM

Answers

  • create table #T (validto decimal(18,0))
    insert into #T (validto)
    values (20190824223234),
    (20190826224244),
    (20190825223739),
    (20190820220906),
    (20190821221254),
    (20180714005825),
    (20190823222907),
    (20190822221834),
    (20190827224611)
    
    select 
    cast(stuff(stuff(stuff (Cast(validto as varchar(20)), 9,0,' '),12,0,':'),15,0,':') as datetime2(0))
    from #T
    drop table #T

    • Marked as answer by Royal Thomas Thursday, August 29, 2019 9:38 PM
    Thursday, August 29, 2019 9:36 PM
  • Combine the following outputs:

    DECLARE @d decimal = 20190824223234;
    SELECT
     CONVERT(varchar(14), @d) AS [DatetimeInString],
     LEFT(CONVERT(varchar(14), @d), 4) AS [Year],
     SUBSTRING(CONVERT(varchar(14), @d), 5, 2) AS [Month],
     SUBSTRING(CONVERT(varchar(14), @d), 7, 2) AS [Day],
     SUBSTRING(CONVERT(varchar(14), @d), 9, 2) AS [Hour],
     SUBSTRING(CONVERT(varchar(14), @d), 11, 2) AS [Minute],
     SUBSTRING(CONVERT(varchar(14), @d), 13, 2) AS [Second]

    DECLARE @d decimal = 20190824223234;
    SELECT
     LEFT(CONVERT(varchar(14), @d), 4) + '-' +
     SUBSTRING(CONVERT(varchar(14), @d), 5, 2) + '-' +
     SUBSTRING(CONVERT(varchar(14), @d), 7, 2) + ' ' +
     SUBSTRING(CONVERT(varchar(14), @d), 9, 2) + ':' +
     SUBSTRING(CONVERT(varchar(14), @d), 11, 2) + ':' +
     SUBSTRING(CONVERT(varchar(14), @d), 13, 2) AS [Datetime]


    A Fan of SSIS, SSRS and SSAS


    • Marked as answer by Royal Thomas Thursday, August 29, 2019 9:38 PM
    • Edited by Guoxiong Yuan Thursday, August 29, 2019 9:39 PM
    Thursday, August 29, 2019 9:37 PM

All replies

  • create table #T (validto decimal(18,0))
    insert into #T (validto)
    values (20190824223234),
    (20190826224244),
    (20190825223739),
    (20190820220906),
    (20190821221254),
    (20180714005825),
    (20190823222907),
    (20190822221834),
    (20190827224611)
    
    select 
    cast(stuff(stuff(stuff (Cast(validto as varchar(20)), 9,0,' '),12,0,':'),15,0,':') as datetime2(0))
    from #T
    drop table #T

    • Marked as answer by Royal Thomas Thursday, August 29, 2019 9:38 PM
    Thursday, August 29, 2019 9:36 PM
  • Combine the following outputs:

    DECLARE @d decimal = 20190824223234;
    SELECT
     CONVERT(varchar(14), @d) AS [DatetimeInString],
     LEFT(CONVERT(varchar(14), @d), 4) AS [Year],
     SUBSTRING(CONVERT(varchar(14), @d), 5, 2) AS [Month],
     SUBSTRING(CONVERT(varchar(14), @d), 7, 2) AS [Day],
     SUBSTRING(CONVERT(varchar(14), @d), 9, 2) AS [Hour],
     SUBSTRING(CONVERT(varchar(14), @d), 11, 2) AS [Minute],
     SUBSTRING(CONVERT(varchar(14), @d), 13, 2) AS [Second]

    DECLARE @d decimal = 20190824223234;
    SELECT
     LEFT(CONVERT(varchar(14), @d), 4) + '-' +
     SUBSTRING(CONVERT(varchar(14), @d), 5, 2) + '-' +
     SUBSTRING(CONVERT(varchar(14), @d), 7, 2) + ' ' +
     SUBSTRING(CONVERT(varchar(14), @d), 9, 2) + ':' +
     SUBSTRING(CONVERT(varchar(14), @d), 11, 2) + ':' +
     SUBSTRING(CONVERT(varchar(14), @d), 13, 2) AS [Datetime]


    A Fan of SSIS, SSRS and SSAS


    • Marked as answer by Royal Thomas Thursday, August 29, 2019 9:38 PM
    • Edited by Guoxiong Yuan Thursday, August 29, 2019 9:39 PM
    Thursday, August 29, 2019 9:37 PM
  • Here is a solution that uses datetimefromparts. I will have to admit that it was more work than I expected. :-) I use the power function to save me from counting zeroes.

    create table #T (validto decimal)
    insert into #T (validto)
    values (20190824223234),
    (20190826224244),
    (20190825223739),
    (20190820220906),
    (20190821221254),
    (20180714005825),
    (20190823222907),
    (20190822221834),
    (20191227224611) 
    
    SELECT validto,
           datetime2fromparts(validto / power(big10, 10),
                             validto % power(big10, 10)  / power(big10, 8),
                             validto % power(big10,  8)  / power(big10, 6),
                             validto % power(big10,  6)  / power(big10, 4),
                             validto % power(big10,  4)  / power(big10, 2),
                             validto % power(big10,  2), 0, 0)  
    FROM (SELECT convert(bigint, validto) AS validto 
          FROM #T) AS T
    CROSS JOIN (SELECT convert(bigint, 10) AS big10) AS B
    go
    DROP TABLE #T
    
    


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, August 29, 2019 9:59 PM