# Convert decimal to date time

• ### 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

• ```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 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 Thursday, August 29, 2019 9:38 PM
• Edited by 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 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 Thursday, August 29, 2019 9:38 PM
• Edited by 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