March 13, 2018 at 3:41 pm
Hi,
What's the easiest way to take 2 numeric fields and display them as a DATETIME field. I have tried many different convert statements but keep getting 'arithmetic overflow when converting' errors. Both fields are numeric(8) and and I need them to display as DATETIME.
Date Time
---------------------------
20180209 161517
20180209 164505
I need to display them as:
2018-02-09 16:15:17:00
2018-02-09 16:45:05:00
Thanks!
Bea Isabelle
March 13, 2018 at 3:57 pm
You should be able to use convert but you'll probably need to do a little bit of text formatting first since that's not a format SQL Server can read.
March 13, 2018 at 4:14 pm
One way to do it:
CREATE TABLE #tmpdata(MyDate INT, MyTime INT);
INSERT INTO [#tmpdata]([MyDate],[MyTime])
VALUES (20180209,161517),(20180209,164505);
SELECT
[t].[MyDate]
, [t].[MyTime]
, STUFF(STUFF(CONVERT(VARCHAR(8), [t].[MyDate]),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(RIGHT('0' + CONVERT(VARCHAR(6), [t].[MyTime]),6),5,0,':'),3,0,':') + ':00'
FROM
[#tmpdata] AS [t];
DROP TABLE [#tmpdata];
Or
CREATE TABLE #tmpdata(MyDate INT, MyTime INT);
INSERT INTO [#tmpdata]([MyDate],[MyTime])
VALUES (20180209,161517),(20180209,164505);
SELECT
[t].[MyDate]
, [t].[MyTime]
, cast(STUFF(STUFF(CONVERT(VARCHAR(8), [t].[MyDate]),7,0,'-'),5,0,'-') + ' ' + STUFF(STUFF(RIGHT('0' + CONVERT(VARCHAR(6), [t].[MyTime]),6),5,0,':'),3,0,':') as datetime)
FROM
[#tmpdata] AS [t];
DROP TABLE [#tmpdata];
March 13, 2018 at 4:37 pm
Well, that certainly did it. Thank you Lynn...I never would have thought to use the STUFF command. 🙂
(No column name)
2018-02-09 16:51:17:00
2018-02-09 16:51:17:00
2018-02-09 16:51:17:00
2018-02-09 16:51:17:00
2018-02-09 16:51:17:00
Thanks!
Bea Isabelle
March 13, 2018 at 6:39 pm
I remember seeing an example by Peter "Peso" Larsson a long time ago, adopted his method, and tweaked it a bit. Here's an iTVF that will do it for you.
CREATE FUNCTION [dbo].[IntsToSSDateTime]
/**********************************************************************************************************************
Purpose:
The function takes an "Integer Date" in the form of YYYYMMDD and "IntegerTime" in the form of HHMMSS similar to those
found in the MSDB.dbo.SysJobHistory table (Run_Date and Run_Time columns) and converts them into an SQL Server
DATETIME datatype.
The return is a single element table known as an "iSF" or "Inline Scalar Function" and is used as if it were an "iTVF"
or "Table Valued Function" even though it returns only a scalar value. This prevents the overhead of a normal scalar
function and allows for very high performance.
The performance is further enhanced because the calculations are all done using integer and true date math instead of
any excursions to the VARCHAR world.
Programmer's Notes:
1. You must use a full 8 digit date in the form of YYYYMMDD.
2. You do not need to use a full 6 digit time (24 hour time is used) but should be filled from the right in the form
of HHMMSS. For example, if you want a time of 00:00:09, then you only need the "9". If you want 10:00:00, then you
must include all six digits as 100000. (Just like they do in the SysJobsHistory table in MSDB).
Usage (example w/MSDB):
SELECT hst.run_date, hst.run_time, ssdt.SSDateTime
FROM msdb.dbo.sysjobhistory hst
CROSS APPLY util.IntsToSSDateTime(run_date,run_time) ssdt
Revision History:
Rev 00 - 02 Oct 2012 - Jeff Moden - Initial Creation
Rev 01 - 11 Apr 2013 - Jeff Moden
- Microsoft uses "0" instead of NULL for missing dates. Added code to change those to NULL.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pIntegerDate INT,
@pIntegerTime INT
)
RETURNS TABLE WITH SCHEMABINDING AS
--===== Convert the date/time to a DATETIME datatype and return it as a single element table.
-- Note that 22801 is the number of months from 0000 to 1900 + 1 month.
-- The rest of the caclulations are just standard math for breaking an integer apart.
-- In case you didn't know, the "%" operator is MODULO and "0" is the same as 1900-01-01.
RETURN
SELECT SSDateTime =
DATEADD(mm,NULLIF(@pIntegerDate,0)/10000*12-22801 --Years to Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%10000/100, 0) --Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%100-1 --Days (Rev 01)
+ DATEADD(ss,@pIntegerTime/10000*3600 --Hours to seconds
+ @pIntegerTime%10000/100*60 --Minutes to seconds
+ @pIntegerTime%100, 0) --Seconds
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2018 at 2:43 am
Jeff Moden - Tuesday, March 13, 2018 6:39 PMI remember seeing an example by Peter "Peso" Larsson a long time ago, adopted his method, and tweaked it a bit. Here's an iTVF that will do it for you.
CREATE FUNCTION [dbo].[IntsToSSDateTime]
/**********************************************************************************************************************
Purpose:
The function takes an "Integer Date" in the form of YYYYMMDD and "IntegerTime" in the form of HHMMSS similar to those
found in the MSDB.dbo.SysJobHistory table (Run_Date and Run_Time columns) and converts them into an SQL Server
DATETIME datatype.The return is a single element table known as an "iSF" or "Inline Scalar Function" and is used as if it were an "iTVF"
or "Table Valued Function" even though it returns only a scalar value. This prevents the overhead of a normal scalar
function and allows for very high performance.The performance is further enhanced because the calculations are all done using integer and true date math instead of
any excursions to the VARCHAR world.Programmer's Notes:
1. You must use a full 8 digit date in the form of YYYYMMDD.
2. You do not need to use a full 6 digit time (24 hour time is used) but should be filled from the right in the form
of HHMMSS. For example, if you want a time of 00:00:09, then you only need the "9". If you want 10:00:00, then you
must include all six digits as 100000. (Just like they do in the SysJobsHistory table in MSDB).Usage (example w/MSDB):
SELECT hst.run_date, hst.run_time, ssdt.SSDateTime
FROM msdb.dbo.sysjobhistory hst
CROSS APPLY util.IntsToSSDateTime(run_date,run_time) ssdtRevision History:
Rev 00 - 02 Oct 2012 - Jeff Moden - Initial Creation
Rev 01 - 11 Apr 2013 - Jeff Moden
- Microsoft uses "0" instead of NULL for missing dates. Added code to change those to NULL.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pIntegerDate INT,
@pIntegerTime INT
)
RETURNS TABLE WITH SCHEMABINDING AS
--===== Convert the date/time to a DATETIME datatype and return it as a single element table.
-- Note that 22801 is the number of months from 0000 to 1900 + 1 month.
-- The rest of the caclulations are just standard math for breaking an integer apart.
-- In case you didn't know, the "%" operator is MODULO and "0" is the same as 1900-01-01.
RETURN
SELECT SSDateTime =
DATEADD(mm,NULLIF(@pIntegerDate,0)/10000*12-22801 --Years to Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%10000/100, 0) --Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%100-1 --Days (Rev 01)+ DATEADD(ss,@pIntegerTime/10000*3600 --Hours to seconds
+ @pIntegerTime%10000/100*60 --Minutes to seconds
+ @pIntegerTime%100, 0) --Seconds
;
There is a more efficient way of doing this using the datefromparts / datetimefromparts functions.
😎
The code is self explanatory:
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @SAMPLEDATA TABLE
(
XDate INT NOT NULL
,XTime INT NOT NULL
);
INSERT INTO @SAMPLEDATA (XDate,XTime)
VALUES
(20180209,161517)
,(20180209,164505)
;
SELECT
SD.XDate AS NUM_DATE
,SD.XTime AS NUM_TIME
,SD.XDate / 10000 AS NUM_YEAR
,(SD.XDate % 10000) / 100 AS NUM_MONTH
,(SD.XDate % 100) AS NUM_DAY
,SD.XTime / 10000 AS NUM_HOUR
,(SD.XTime % 10000) / 100 AS NUM_MIN
,(SD.XTime % 100) AS NUM_SEC
,DATEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100)) AS NUM_TO_DATE
,DATETIMEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100),(SD.XTime / 10000),((SD.XTime % 10000) / 100),(SD.XTime % 100),0) AS NUM_TO_DATETIME
FROM @SAMPLEDATA SD;
Output
NUM_DATE NUM_TIME NUM_YEAR NUM_MONTH NUM_DAY NUM_HOUR NUM_MIN NUM_SEC NUM_TO_DATE NUM_TO_DATETIME
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------------
20180209 161517 2018 2 9 16 15 17 2018-02-09 2018-02-09 16:15:17.000
20180209 164505 2018 2 9 16 45 5 2018-02-09 2018-02-09 16:45:05.000
March 14, 2018 at 8:38 am
Eirikur Eiriksson - Wednesday, March 14, 2018 2:43 AMJeff Moden - Tuesday, March 13, 2018 6:39 PMI remember seeing an example by Peter "Peso" Larsson a long time ago, adopted his method, and tweaked it a bit. Here's an iTVF that will do it for you.
CREATE FUNCTION [dbo].[IntsToSSDateTime]
/**********************************************************************************************************************
Purpose:
The function takes an "Integer Date" in the form of YYYYMMDD and "IntegerTime" in the form of HHMMSS similar to those
found in the MSDB.dbo.SysJobHistory table (Run_Date and Run_Time columns) and converts them into an SQL Server
DATETIME datatype.The return is a single element table known as an "iSF" or "Inline Scalar Function" and is used as if it were an "iTVF"
or "Table Valued Function" even though it returns only a scalar value. This prevents the overhead of a normal scalar
function and allows for very high performance.The performance is further enhanced because the calculations are all done using integer and true date math instead of
any excursions to the VARCHAR world.Programmer's Notes:
1. You must use a full 8 digit date in the form of YYYYMMDD.
2. You do not need to use a full 6 digit time (24 hour time is used) but should be filled from the right in the form
of HHMMSS. For example, if you want a time of 00:00:09, then you only need the "9". If you want 10:00:00, then you
must include all six digits as 100000. (Just like they do in the SysJobsHistory table in MSDB).Usage (example w/MSDB):
SELECT hst.run_date, hst.run_time, ssdt.SSDateTime
FROM msdb.dbo.sysjobhistory hst
CROSS APPLY util.IntsToSSDateTime(run_date,run_time) ssdtRevision History:
Rev 00 - 02 Oct 2012 - Jeff Moden - Initial Creation
Rev 01 - 11 Apr 2013 - Jeff Moden
- Microsoft uses "0" instead of NULL for missing dates. Added code to change those to NULL.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pIntegerDate INT,
@pIntegerTime INT
)
RETURNS TABLE WITH SCHEMABINDING AS
--===== Convert the date/time to a DATETIME datatype and return it as a single element table.
-- Note that 22801 is the number of months from 0000 to 1900 + 1 month.
-- The rest of the caclulations are just standard math for breaking an integer apart.
-- In case you didn't know, the "%" operator is MODULO and "0" is the same as 1900-01-01.
RETURN
SELECT SSDateTime =
DATEADD(mm,NULLIF(@pIntegerDate,0)/10000*12-22801 --Years to Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%10000/100, 0) --Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%100-1 --Days (Rev 01)+ DATEADD(ss,@pIntegerTime/10000*3600 --Hours to seconds
+ @pIntegerTime%10000/100*60 --Minutes to seconds
+ @pIntegerTime%100, 0) --Seconds
;There is a more efficient way of doing this using the datefromparts / datetimefromparts functions.
😎The code is self explanatory:
USE TEEST;
GO
SET NOCOUNT ON;DECLARE @SAMPLEDATA TABLE
(
XDate INT NOT NULL
,XTime INT NOT NULL
);
INSERT INTO @SAMPLEDATA (XDate,XTime)
VALUES
(20180209,161517)
,(20180209,164505)
;SELECT
SD.XDate AS NUM_DATE
,SD.XTime AS NUM_TIME
,SD.XDate / 10000 AS NUM_YEAR
,(SD.XDate % 10000) / 100 AS NUM_MONTH
,(SD.XDate % 100) AS NUM_DAY
,SD.XTime / 10000 AS NUM_HOUR
,(SD.XTime % 10000) / 100 AS NUM_MIN
,(SD.XTime % 100) AS NUM_SEC
,DATEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100)) AS NUM_TO_DATE
,DATETIMEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100),(SD.XTime / 10000),((SD.XTime % 10000) / 100),(SD.XTime % 100),0) AS NUM_TO_DATETIME
FROM @SAMPLEDATA SD;Output
NUM_DATE NUM_TIME NUM_YEAR NUM_MONTH NUM_DAY NUM_HOUR NUM_MIN NUM_SEC NUM_TO_DATE NUM_TO_DATETIME
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------------
20180209 161517 2018 2 9 16 15 17 2018-02-09 2018-02-09 16:15:17.000
20180209 164505 2018 2 9 16 45 5 2018-02-09 2018-02-09 16:45:05.000
I keep forgetting about the new functions. Comes from supporting the older versions.
March 14, 2018 at 9:42 am
Lynn Pettis - Wednesday, March 14, 2018 8:38 AMEirikur Eiriksson - Wednesday, March 14, 2018 2:43 AMJeff Moden - Tuesday, March 13, 2018 6:39 PMI remember seeing an example by Peter "Peso" Larsson a long time ago, adopted his method, and tweaked it a bit. Here's an iTVF that will do it for you.
CREATE FUNCTION [dbo].[IntsToSSDateTime]
/**********************************************************************************************************************
Purpose:
The function takes an "Integer Date" in the form of YYYYMMDD and "IntegerTime" in the form of HHMMSS similar to those
found in the MSDB.dbo.SysJobHistory table (Run_Date and Run_Time columns) and converts them into an SQL Server
DATETIME datatype.The return is a single element table known as an "iSF" or "Inline Scalar Function" and is used as if it were an "iTVF"
or "Table Valued Function" even though it returns only a scalar value. This prevents the overhead of a normal scalar
function and allows for very high performance.The performance is further enhanced because the calculations are all done using integer and true date math instead of
any excursions to the VARCHAR world.Programmer's Notes:
1. You must use a full 8 digit date in the form of YYYYMMDD.
2. You do not need to use a full 6 digit time (24 hour time is used) but should be filled from the right in the form
of HHMMSS. For example, if you want a time of 00:00:09, then you only need the "9". If you want 10:00:00, then you
must include all six digits as 100000. (Just like they do in the SysJobsHistory table in MSDB).Usage (example w/MSDB):
SELECT hst.run_date, hst.run_time, ssdt.SSDateTime
FROM msdb.dbo.sysjobhistory hst
CROSS APPLY util.IntsToSSDateTime(run_date,run_time) ssdtRevision History:
Rev 00 - 02 Oct 2012 - Jeff Moden - Initial Creation
Rev 01 - 11 Apr 2013 - Jeff Moden
- Microsoft uses "0" instead of NULL for missing dates. Added code to change those to NULL.
**********************************************************************************************************************/
--===== Declare the I/O for the function
(
@pIntegerDate INT,
@pIntegerTime INT
)
RETURNS TABLE WITH SCHEMABINDING AS
--===== Convert the date/time to a DATETIME datatype and return it as a single element table.
-- Note that 22801 is the number of months from 0000 to 1900 + 1 month.
-- The rest of the caclulations are just standard math for breaking an integer apart.
-- In case you didn't know, the "%" operator is MODULO and "0" is the same as 1900-01-01.
RETURN
SELECT SSDateTime =
DATEADD(mm,NULLIF(@pIntegerDate,0)/10000*12-22801 --Years to Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%10000/100, 0) --Months (Rev 01)
+ NULLIF(@pIntegerDate,0)%100-1 --Days (Rev 01)+ DATEADD(ss,@pIntegerTime/10000*3600 --Hours to seconds
+ @pIntegerTime%10000/100*60 --Minutes to seconds
+ @pIntegerTime%100, 0) --Seconds
;There is a more efficient way of doing this using the datefromparts / datetimefromparts functions.
😎The code is self explanatory:
USE TEEST;
GO
SET NOCOUNT ON;DECLARE @SAMPLEDATA TABLE
(
XDate INT NOT NULL
,XTime INT NOT NULL
);
INSERT INTO @SAMPLEDATA (XDate,XTime)
VALUES
(20180209,161517)
,(20180209,164505)
;SELECT
SD.XDate AS NUM_DATE
,SD.XTime AS NUM_TIME
,SD.XDate / 10000 AS NUM_YEAR
,(SD.XDate % 10000) / 100 AS NUM_MONTH
,(SD.XDate % 100) AS NUM_DAY
,SD.XTime / 10000 AS NUM_HOUR
,(SD.XTime % 10000) / 100 AS NUM_MIN
,(SD.XTime % 100) AS NUM_SEC
,DATEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100)) AS NUM_TO_DATE
,DATETIMEFROMPARTS((SD.XDate / 10000),((SD.XDate % 10000) / 100),(SD.XDate % 100),(SD.XTime / 10000),((SD.XTime % 10000) / 100),(SD.XTime % 100),0) AS NUM_TO_DATETIME
FROM @SAMPLEDATA SD;Output
NUM_DATE NUM_TIME NUM_YEAR NUM_MONTH NUM_DAY NUM_HOUR NUM_MIN NUM_SEC NUM_TO_DATE NUM_TO_DATETIME
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------------
20180209 161517 2018 2 9 16 15 17 2018-02-09 2018-02-09 16:15:17.000
20180209 164505 2018 2 9 16 45 5 2018-02-09 2018-02-09 16:45:05.000I keep forgetting about the new functions. Comes from supporting the older versions.
Did a performance comparison between the different methods few years back, if I remember correctly, this was the second fastest method, float to datetime was faster but more complicated to implement. Will ping back when I find the stuff.
😎
These days, one has to spend at least two hours every day, just to keep up, no shame in missing some of the new functionalities. Supporting older versions even makes it harder, now running 7 to 2017 in my home lab, eight versions all together, and that is only the MS-Sql Server server instances, Oracle from 7... :crazy:
March 14, 2018 at 9:48 am
Thanks for all the feedback. I will test these suggestions as well...always good to learn new things! You guys are awesome 🙂
Thanks!
Bea Isabelle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply