September 16, 2015 at 3:17 pm
I have a very awful table structure that I have to deal with. This is in an ERP and I can't change the tables...yes I know how completely horrible this is. This system stores dates and times in separate columns. This in itself isn't so bad but they are always stored as numerics. This causes some very ugly code to turn this horrible design into a datetime value that is actually usable. I have come up with a couple of ways of turning into a datetime but I am looking for other ideas in case somebody comes up with a better idea.
Here is my table and data. The first two columns are the Date and Time values stored as numerics. The last column is the datetime I need this to be.
CREATE TABLE #Something
(
VHRGDT numeric(8, 0) NOT NULL,
VHRGTM numeric(6, 0) NOT NULL,
DesiredDateTimeValue datetime
)
insert #Something
select 20150817, 134818, '2015-08-17 13:48:18.000' union all
select 20150818, 71406, '2015-08-18 07:14:06.000' union all
select 20150818, 141636, '2015-08-18 14:16:36.000' union all
select 20150819, 74938, '2015-08-19 07:49:38.000'
select *
from #Something
Everything I have come up with is a mess of cast and stuff basically turning this mess into a string that can ultimately be cast as a datetime. Maybe some fresh eyes on this will come up with something better.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 3:34 pm
Try the FORMAT function. It was added in SQL 2012.
SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *
FROM #Something s
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 16, 2015 at 3:37 pm
drew.allen (9/16/2015)
Try the FORMAT function. It was added in SQL 2012.
SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *
FROM #Something s
Drew
Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 5:24 pm
Or this if you want to use a MS provided scalar function:
select *, msdb.dbo.agent_datetime(VHRGDT,VHRGTM)
from #Something
September 16, 2015 at 8:17 pm
Lynn Pettis (9/16/2015)
Or this if you want to use a MS provided scalar function:
select *, msdb.dbo.agent_datetime(VHRGDT,VHRGTM)
from #Something
Coincidentally just been looking at something that used that function and I went to take a look at it
msdb.dbo.sp_helptext agent_datetime
and then wished that I hadn't! I suppose if it works that's good enough ... although my code barfed trying to use that function as it didn't have EXEC permission to MSDB 🙁 I imagine I could have solved that ...
... but somewhere on my travels I saw this which satisfies my perception that an arithmetic algorithm will perform better than a string one, but the formula is a bit Smoke & Mirrors to me
CONVERT(datetime, RTRIM(VHRGDT))
+ (VHRGTM * 9 +VHRGTM % 10000 * 6 + VHRGTM % 100 * 10)
/ 216e4
Given that the date will only ever be 8 digits (as an INT column), I assume that RTrim is being used just to cast INT to VARCHAR ... I don't know if there is a reason for using RTrim - perhaps it performs better than CONVERT(varchar(8), VHRGDT)?
September 16, 2015 at 8:29 pm
Sean, I can't wait to have a go at this one in the morning. I'll attempt a 2008 version and see what kind of performance I can get out of it.
September 16, 2015 at 8:42 pm
Sean Lange (9/16/2015)
I have a very awful table structure that I have to deal with. This is in an ERP and I can't change the tables...yes I know how completely horrible this is. This system stores dates and times in separate columns. This in itself isn't so bad but they are always stored as numerics. This causes some very ugly code to turn this horrible design into a datetime value that is actually usable. I have come up with a couple of ways of turning into a datetime but I am looking for other ideas in case somebody comes up with a better idea.Here is my table and data. The first two columns are the Date and Time values stored as numerics....
Quick suggestion, create an iTVF for the conversion using a mix of convert and format string convertion, performs quite nicely
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TVFN_INT_DATE_TIME_TO_DATETIME') IS NOT NULL DROP FUNCTION dbo.TVFN_INT_DATE_TIME_TO_DATETIME;
IF OBJECT_ID('tempdb..#Something') IS NOT NULL DROP TABLE tempdb..#Something;
CREATE TABLE #Something
(
VHRGDT numeric(8, 0) NOT NULL,
VHRGTM numeric(6, 0) NOT NULL,
DesiredDateTimeValue datetime
)
insert #Something
select 20150817, 134818, '2015-08-17 13:48:18.000' union all
select 20150818, 71406, '2015-08-18 07:14:06.000' union all
select 20150818, 141636, '2015-08-18 14:16:36.000' union all
select 20150819, 74938, '2015-08-19 07:49:38.000'
GO
CREATE FUNCTION dbo.TVFN_INT_DATE_TIME_TO_DATETIME
(
@DATEINT INT
,@TIMEINT INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
/* Replaced FORMAT(@TIMEINT,' 00:00:00.000') with Stuff and convert after Jeff Moden pointed
out the poor performance of the FORMAT function
*/
SELECT CONVERT(DATETIME,CONVERT(VARCHAR(8),@DATEINT,0) + CHAR(32)
+ STUFF(STUFF(STUFF(CONVERT(VARCHAR(8)
,10000000 + @TIMEINT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,''),112) AS DTVALUE
GO
SELECT
SG.VHRGDT
,SG.VHRGTM
,SG.DesiredDateTimeValue
,DTOUT.DTVALUE
FROM #Something SG
CROSS APPLY dbo.TVFN_INT_DATE_TIME_TO_DATETIME(SG.VHRGDT,SG.VHRGTM) AS DTOUT;
Output
VHRGDT VHRGTM DesiredDateTimeValue DTVALUE
--------- -------- ----------------------- -----------------------
20150817 134818 2015-08-17 13:48:18.000 2015-08-17 13:48:18.000
20150818 71406 2015-08-18 07:14:06.000 2015-08-18 07:14:06.000
20150818 141636 2015-08-18 14:16:36.000 2015-08-18 14:16:36.000
20150819 74938 2015-08-19 07:49:38.000 2015-08-19 07:49:38.000
September 16, 2015 at 10:57 pm
Sean Lange (9/16/2015)
drew.allen (9/16/2015)
Try the FORMAT function. It was added in SQL 2012.
SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *
FROM #Something s
Drew
Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:
DO NOT USE FORMAT!!!! PERIOD!!!! It takes 44 times longer than either CAST or CONVERT. I suspect that it's a piece of really bad Regex behind the scenes.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2015 at 1:01 am
Jeff Moden (9/16/2015)
Sean Lange (9/16/2015)
drew.allen (9/16/2015)
Try the FORMAT function. It was added in SQL 2012.
SELECT FORMAT(vhrgdt, '0000-00-00') + ' ' + FORMAT(vhrgtm, '00:00:00.000'), *
FROM #Something s
Drew
Thanks Drew. I don't use FORMAT very often but that is certainly a mountain simpler than what I was doing. The code I had worked but was just awful because I basically did all that formatting manually. :w00t:
DO NOT USE FORMAT!!!! PERIOD!!!! It takes 44 times longer than either CAST or CONVERT. I suspect that it's a piece of really bad Regex behind the scenes.
Thanks so much for the heads up on the FORMAT Jeff, I had totally forgotten about that one and BTW the difference is closer to 100 times.
😎
Just in case anyone is considering using that format hog, have a look at this:
USE tempdb;
GO
SET NOCOUNT ON
/* Format function vs. string conversion */
IF OBJECT_ID('dbo.TBL_SAMPLE_TIME_INT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_TIME_INT;
CREATE TABLE dbo.TBL_SAMPLE_TIME_INT
(
STI_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_TIME_INT_STI_ID PRIMARY KEY CLUSTERED
,STI_TIME_INT INT NOT NULL
);
DECLARE @TTIME TIME = CONVERT(TIME,'00:00:00',0);
INSERT INTO dbo.TBL_SAMPLE_TIME_INT(STI_TIME_INT)
SELECT TOP(1000000)
CONVERT(INT,REPLACE(CONVERT(VARCHAR(8),DATEADD(SECOND,CHECKSUM(NEWID()) % 864000,@TTIME),121),CHAR(58),''),0)
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2
CROSS JOIN sys.all_columns SAC3
;
DECLARE @CHAR_BUCKET VARCHAR(8) = '';
RAISERROR('---FORMAT 1 ---',0,0) WITH NOWAIT;
SET STATISTICS TIME ON;
SELECT
@CHAR_BUCKET = FORMAT(STI.STI_TIME_INT,' 00:00:00.000')
FROM dbo.TBL_SAMPLE_TIME_INT STI;
SET STATISTICS TIME OFF;
RAISERROR('---CONVERT 1 ---',0,0) WITH NOWAIT;
SET STATISTICS TIME ON;
SELECT
@CHAR_BUCKET = STUFF(STUFF(STUFF(CONVERT(VARCHAR(8),10000000 + STI.STI_TIME_INT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,'')
FROM dbo.TBL_SAMPLE_TIME_INT STI;
SET STATISTICS TIME OFF;
RAISERROR('---FORMAT 2 ---',0,0) WITH NOWAIT;
SET STATISTICS TIME ON;
SELECT
@CHAR_BUCKET = FORMAT(STI.STI_TIME_INT,' 00:00:00.000')
FROM dbo.TBL_SAMPLE_TIME_INT STI;
SET STATISTICS TIME OFF;
RAISERROR('---CONVERT 2 ---',0,0) WITH NOWAIT;
SET STATISTICS TIME ON;
SELECT
@CHAR_BUCKET = STUFF(STUFF(STUFF(CONVERT(VARCHAR(8),10000000 + STI.STI_TIME_INT,0),5,0,CHAR(58)),8,0,CHAR(58)),1,2,'')
FROM dbo.TBL_SAMPLE_TIME_INT STI;
SET STATISTICS TIME OFF;
Output
---FORMAT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 18221 ms, elapsed time = 21281 ms.
---CONVERT 1 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 274 ms.
---FORMAT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 18876 ms, elapsed time = 21356 ms.
---CONVERT 2 ---
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 274 ms.
September 17, 2015 at 2:55 am
Hi Sean, was your code as nasty looking as mine below?
SELECT
DATEADD(SECOND, CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),1,2)) * 60 * 60 + CONVERT(INT, SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),3,2)) * 60 + SUBSTRING(RIGHT('0' +(CONVERT(VARCHAR(6), VHRGTM)),6),5,2), CONVERT(DATETIME, CONVERT(VARCHAR(8), VHRGDT),113)),
DesiredDateTimeValue
FROM #Something
September 17, 2015 at 3:20 am
Daytime here, so my servers are under load and I cannot get a consistent runtime, but this one looks to perform best for me.
SELECTDateTimeFromParts(VHRGDT / 10000, (VHRGDT % 10000) / 100, VHRGDT % 100
, VHRGTM / 10000, (VHRGTM / 100) % 100, VHRGTM % 100, 0)
, DesiredDateTimeValue
FROM#Something
September 17, 2015 at 3:24 am
Another option for you....
SELECT CONVERT( DATETIME, CONVERT(VARCHAR(8), VHRGDT), 113) + CONVERT(DATETIME, STUFF(STUFF(RIGHT('0'+CONVERT(VARCHAR(6), VHRGTM), 6),3,0,':'),6,0,':'),108) , DesiredDateTimeValue FROM #Something
September 17, 2015 at 4:15 am
Or you could create a lookup table with all 86400 seconds in and then use a query like the one below....
SELECT
S.DesiredDateTimeValue
, CONVERT(DATETIME, CONVERT(VARCHAR(8), S.VHRGDT)) + CONVERT(DATETIME, T.ATime) [DateTime]
FROM #Something S
JOIN Times T on T.VHRGTM = S.VHRGTM
It only takes a couple of seconds to populate the lookup table.
drop table Times;
create table Times
(
ATime time not null
, VHRGTM numeric(6, 0) NOT NULL primary key,
)
set nocount on
declare @time time
set @time = convert(datetime, 0);
while @time < '23:59:59'
begin
insert into times(ATime, VHRGTM) select @time, convert(numeric(6,0), replace(convert(varchar(8), @time, 108), ':',''))
set @time = dateadd(second, 1, @time)
end
insert into times(ATime, VHRGTM) select @time, convert(numeric(6,0), replace(convert(varchar(8), @time, 108), ':',''))
September 17, 2015 at 4:22 am
I understand not being able to modify the existing tables , but would it be possible the simple add 2 date tables with a set of dates and times , I am assuming seconds resolution isnt important here. And them simply join the columns as needed , I think this would help for lookups as well as scans be probably behave much better with filters.
September 17, 2015 at 6:33 am
Here's the function I came up with. It first left-pads the integers with zeros out to the length they should be to accommodate the case where you have a year less than 1000 or an hour less than 10, resulting in an integer that's too short. It then parses and formats the strings and converts the output.
IF OBJECT_ID('dbo.IntegersToDatetime', 'if') IS NOT NULL DROP FUNCTION dbo.IntegersToDatetime;
go
CREATE FUNCTION dbo.IntegersToDatetime(@intDate Integer,
@intTime Integer) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
WITH cteConversions AS (
SELECT normalized_date = RIGHT('00000000' + CONVERT(Varchar(8), NULLIF(@intDate, 0)), 8), --CONVERT(char(8), NULLIF(@intDate, 0)),
normalized_time = RIGHT('000000' + CONVERT(Varchar(6), NULLIF(@intTime, 0)), 6)
),
cteFormatted AS (
SELECT ProperDate = SUBSTRING(normalized_date, 1, 4) + '/' + SUBSTRING(normalized_date, 5, 2) + '/' + SUBSTRING(normalized_date, 7, 2),
ProperTime = SUBSTRING(normalized_time, 1, 2) + ':' + SUBSTRING(normalized_time, 3, 2) + ':' + SUBSTRING(normalized_time, 5, 2)
FROM cteConversions
)
SELECT ProperDate, ProperTime, CONVERT(Datetime, ProperDate + ' ' + ProperTime) ProperDateTime
FROM cteFormatted);
go
To test it, I created a table with column names that don't remind me of days past. Sean, I think you know what I'm talking about. 😉
IF OBJECT_ID('dbo.Something') IS NOT NULL DROP TABLE dbo.Something;
CREATE TABLE dbo.Something (
id integer identity(1, 1) NOT NULL,
CONSTRAINT Something_PK PRIMARY KEY CLUSTERED (id),
integer_date integer not null,
integer_time integer not null);
I then loaded it up with 1M rows using Eirikur's excellent formula for the time. The dbo.TallyN is an implementation of Itzik Ben-Gan's virtual tally table using cascading CTEs that results in zero reads. Sean, I'm sure you have one already, but if anyone else needs it, it's published at http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers.
DECLARE @TTIME TIME = CONVERT(TIME,'00:00:00', 0);
INSERT INTO dbo.Something(integer_date, integer_time)
SELECT CONVERT(Integer, Convert(Varchar(10), DATEADD(day, d.N, '01/01/1900'), 112)),
CONVERT(Integer, REPLACE(CONVERT(Varchar(8), DATEADD(second, CHECKSUM(NEWID()) % 864000, @TTIME), 121), CHAR(58), ''),0)
FROM dbo.TallyN(1000000) d;
Overall, the performance wasn't terrible:
SQL Server Execution Times:
CPU time = 1779 ms, elapsed time = 3987 ms.
There was a little variance in the times, but not much. I know the function isn't the prettiest in the world, but it's inside an ITVF, so you won't have to look at it every time. Will this work for you?
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply