How to concatenate date and time values and place results in another field
-
I am trying to concatentate date and time values which are currently created as Integer type fields and place the results into another field in the format YYYY-MM-dd hh:mm:ss
So the table looks like the following:
And I would like the end result to look like the following:
I have included sample data and fields from the original table.
CREATE TABLE #tmpTable (
DSTRCT_CODE int,
WORK_ORDER int,
LAST_MOD_DATE int,
LAST_MOD_TIME int,
LAST_MODIFICATION_DT varchar(255))
INSERT #tmpTable VALUES
(85,10000007,20210129,193402,NULL),
(74,10000002,20181214,142254,NULL),
(18,10000009,NULL,NULL,NULL),
(14,10000008,NULL,NULL,NULL)
SELECT * FROM #tmpTableAny thoughts?
-
Community,
I should mention that the LAST_MODIFICATION_DT will be a datetime2 field as opposed the what you see as VARCHAR(255)
-
Not very pretty, but gets the job done:
DROP TABLE IF EXISTS #tmpTable;
CREATE TABLE #tmpTable
(
DSTRCT_CODE INT
,WORK_ORDER INT
,LAST_MOD_DATE INT
,LAST_MOD_TIME INT
,LAST_MODIFICATION_DT DATETIME2
);
INSERT #tmpTable
VALUES
(85, 10000007, 20210129, 193402, NULL)
,(74, 10000002, 20181214, 142254, NULL)
,(18, 10000009, NULL, NULL, NULL)
,(14, 10000008, NULL, NULL, NULL);
UPDATE t
SET t.LAST_MODIFICATION_DT = CAST(CONCAT(
CAST(t.LAST_MOD_DATE AS CHAR(8))
,' '
,STUFF(
STUFF(RIGHT(CONCAT('000000', t.LAST_MOD_TIME), 6), 5, 0, ':')
,3
,0
,':'
)
) AS DATETIME2)
FROM #tmpTable t
WHERE t.LAST_MOD_DATE IS NOT NULL;
SELECT *
FROM #tmpTable tt;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Hi Phil,
Thanks for getting in touch.
I was hoping this could simply be achieved by doing a simple concatenation of the two fields and placing the results in the LAST_MODIFICATION_DT column.
This appears very difficult
-
Hi Phil,
The following did the job perfectly well
update #tmpTable
set LAST_MODIFICATION_DT = format(LAST_MOD_DATE, '0000"-"00"-"00" "') + format(LAST_MOD_TIME, '00":"00":"00')
-
carlton 84646 wrote:
Hi Phil,
The following did the job perfectly well
update #tmpTable set LAST_MODIFICATION_DT = format(LAST_MOD_DATE, '0000"-"00"-"00" "') + format(LAST_MOD_TIME, '00":"00":"00')
Ah, be careful now... You've just made that update somewhere between 22 and 53 times slower. The problem occurs EVERY TIME you use that awful excuse for a function known as FORMAT. It is patently NOT your friend. Even super complex CONVERT formulas will blow the doors off of it.
Try this instead...
SELECT LAST_MOD_DATE
,LAST_MOD_TIME
,LAST_MODIFICATION_DT = DATETIME2FROMPARTS(LAST_MOD_DATE/10000, LAST_MOD_DATE/100%100, LAST_MOD_DATE%100 --Date
,LAST_MOD_TIME/10000, LAST_MOD_TIME/100%100, LAST_MOD_TIME%100 --Time
,0,0) --Fractions and precision
FROM #tmpTable
;And, yeah... that bad boy will work as an index-able persisted computed column, as well.
--===== Conditionally drop the test table just to make reruns in SSMS easier.
DROP TABLE IF EXISTS #tmpTable2
;
GO
--===== Create and populate the table.
-- This table has an Index-able PERSISTED computed column to do the date/time translation for DATETIME2(0)
CREATE TABLE #tmpTable2
(
DSTRCT_CODE INT
,WORK_ORDER INT
,LAST_MOD_DATE INT
,LAST_MOD_TIME INT
,LAST_MODIFICATION_DT AS (DATETIME2FROMPARTS(LAST_MOD_DATE/10000, LAST_MOD_DATE/100%100, LAST_MOD_DATE%100
,LAST_MOD_TIME/10000, LAST_MOD_TIME/100%100, LAST_MOD_TIME%100
,0,0)) PERSISTED
)
;
--===== Populate the non-computed columns with the original test data
-- and a few more row just to demo that the computed column does work.
INSERT #tmpTable2
(DSTRCT_CODE, WORK_ORDER, LAST_MOD_DATE,LAST_MOD_TIME)
VALUES (85,10000007,20210129,193402)
,(74,10000002,20181214,142254)
,(18,10000009,20181214,254)
,(14,10000008,20180101,54)
,(14,10000006,20180601,1)
,(14,10000010,NULL,NULL)
;
--===== Let's see what we end up with.
SELECT *
FROM #tmpTable2
;Here are the results after the INSERT:
You can read up on DATETIME2FROMPARTS function and what the "%" sign does in the following. The rest is just integer division.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/divide-transact-sql?view=sql-server-ver15 where is says...
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
Keep in mind that all "modulo" is is the remainder from division.
Also keep in mind that anytime you convert numeric values to any character based DataType, you're going to slow things down, sometimes substantially. FORMAT is the very definition of "substantially slower". It's worth knowing and practicing the Integer Math even though it's sometimes a bit longer to write, like it was in this example. It certainly removes a cut or two from the "Death from Thousand Cuts" that most databases suffer from.
And please don't think it's ok if there are a small number of rows involved. It's not. It's a part of what makes up that "Death by a Thousand Cuts" I was talking about.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff,
You're very thorough.
This is very inciteful, and duly noted.
Thanks
-
Another possible way to construct the time part of the value:
CAST(LAST_MOD_DATE AS varchar(8)) + ' ' +
CONVERT(varchar(8), DATEADD(SECOND, (LAST_MOD_TIME % 100) +
(LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600), 0), 8)SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
Quick question, do you need DATETIME2 or is DATETIME enough, I would guess the latter observing the data you posted?
😎
This problem can be sorted with some elementary bit-bashing that is straight forward with the DATETIME data type but since the DATETIME2 has a different byte order, it would be slightly convoluted.
Still that would perform many times faster than the dreaded FORMAT function, that really should be renamed FALLFLAT! 😉
-
ScottPletcher wrote:
Another possible way to construct the time part of the value:
CAST(LAST_MOD_DATE AS varchar(8)) + ' ' +
CONVERT(varchar(8), DATEADD(SECOND, (LAST_MOD_TIME % 100) +
(LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600), 0), 8)I know it doesn't sound like much but, for a million rows with the answer dumped into a throw away variable, that takes 594 ms on my laptop. The integer only method takes only 211 ms... nearly 3 times faster. Again, that doesn't sound earth shaking but imagine if all your code ran 3 times faster. If you can avoid character conversions for such things, it really helps cut down on the "Death by a thousand cuts" in the code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This really works, thanks Jeff
-
Occasionally inciteful.
Always insightful.
🙂
-
carlton 84646 wrote:
This really works, thanks Jeff
You're welcome and thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi Scott/Jeff
Thanks again for reaching out.
I've been revisiting your modification, and I'm not entirely sure how its going to work. Did you forget to add Last_Modification_DT?
CAST(LAST_MOD_DATE AS varchar(8)) + ' ' +
CONVERT(varchar(8), DATEADD(SECOND, (LAST_MOD_TIME % 100) +
(LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600), 0), 8) -
carlton 84646 wrote:
Hi Scott/Jeff
Thanks again for reaching out.
I've been revisiting your modification, and I'm not entirely sure how its going to work. Did you forget to add Last_Modification_DT?
CAST(LAST_MOD_DATE AS varchar(8)) + ' ' +
CONVERT(varchar(8), DATEADD(SECOND, (LAST_MOD_TIME % 100) +
(LAST_MOD_TIME / 100 % 100 * 60 + LAST_MOD_TIME / 10000 * 3600), 0), 8)That is my calc for computing LAST_MODIFICATION_DT from the existing table columns. Is there some problem with it?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply