April 6, 2017 at 12:35 pm
I have to insert hhmmss datetime format into AS400 system in my stored procedure. But my input field does not have seconds and its format is "2017-04-06 14:04:00.000". I tried to append the seconds from current time to the input date field like this....
@LOG_TIME_ORIGINAL = '2017-04-06 14:04:00.000'
REPLACE(CONVERT(VARCHAR(8),@LOG_TIME_ORIGINAL, 108), ':', '')+SUBSTRING(REPLACE(CONVERT(varchar(8),getdate(), 108),':',''),5,7)
but the output is 14040024. It is appending the seconds but with two zeros in front. Its a varchar and why will it append zeros? Is there any other way that I can append current seconds to input datetime?
April 6, 2017 at 12:40 pm
Two possible ways to answer this. But first, why do you want to "pollute your inputs" ? If these times are coming from the past, inferring a current seconds value is 1) presumptuous, 2) non-factual, and 3) not likely to add value in any meaningful way. That said, perhaps the value is relatively real-time and comes from a source that just doesn't include a seconds value, so the first thing you have to do is STUFF the new seconds value over the existing seconds portion of the converted string. Look up the STUFF function online and you should be able to figure out the syntax.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2017 at 1:00 pm
sgmunson - Thursday, April 6, 2017 12:40 PMTwo possible ways to answer this. But first, why do you want to "pollute your inputs" ? If these times are coming from the past, inferring a current seconds value is 1) presumptuous, 2) non-factual, and 3) not likely to add value in any meaningful way. That said, perhaps the value is relatively real-time and comes from a source that just doesn't include a seconds value, so the first thing you have to do is STUFF the new seconds value over the existing seconds portion of the converted string. Look up the STUFF function online and you should be able to figure out the syntax.
Thanks for the reply. Yes you are right...the source does not store seconds. When there are two inputs with in a minute the target system throws an errors as it will not allow duplicate records. To avoid that I had to append the seconds part. I am unable to get the SS format of seconds from current time. Date part give the seconds but i should have zero prefix for seconds 0 to 9.
April 6, 2017 at 1:14 pm
Does this give you the required output?
-- This date is used so that SQL does not get an overflow error when calculating a large time diff in seconds.
DECLARE @CALC_DATE DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0);
DECLARE @LOG_TIME_ORIGINAL DATETIME = '2017-04-06 14:04:00.000';
SELECT LOG_TIME_MODIFIED = DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL)
, LOG_TIME_FORMATTED = REPLACE(CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL), 108), ':', '');
April 6, 2017 at 1:14 pm
arun@D - Thursday, April 6, 2017 1:00 PMsgmunson - Thursday, April 6, 2017 12:40 PMTwo possible ways to answer this. But first, why do you want to "pollute your inputs" ? If these times are coming from the past, inferring a current seconds value is 1) presumptuous, 2) non-factual, and 3) not likely to add value in any meaningful way. That said, perhaps the value is relatively real-time and comes from a source that just doesn't include a seconds value, so the first thing you have to do is STUFF the new seconds value over the existing seconds portion of the converted string. Look up the STUFF function online and you should be able to figure out the syntax.Thanks for the reply. Yes you are right...the source does not store seconds. When there are two inputs with in a minute the target system throws an errors as it will not allow duplicate records. To avoid that I had to append the seconds part. I am unable to get the SS format of seconds from current time. Date part give the seconds but i should have zero prefix for seconds 0 to 9.
Try this:DECLARE @LOG_TIME_ORIGINAL AS datetime = '2017-04-06 14:04:00.000';
SELECT LEFT(REPLACE(CONVERT(varchar(8), @LOG_TIME_ORIGINAL, 108), ':', ''), 4) + SUBSTRING(REPLACE(CONVERT(char(8), getdate(), 108),':',''),5,7);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 6, 2017 at 1:28 pm
DesNorton - Thursday, April 6, 2017 1:14 PMDoes this give you the required output?
-- This date is used so that SQL does not get an overflow error when calculating a large time diff in seconds.
DECLARE @CALC_DATE DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0);DECLARE @LOG_TIME_ORIGINAL DATETIME = '2017-04-06 14:04:00.000';
SELECT LOG_TIME_MODIFIED = DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL)
, LOG_TIME_FORMATTED = REPLACE(CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL), 108), ':', '');
Not exactly....the output is in 12 HR format. But I am looking for 24 hour format.
April 6, 2017 at 3:08 pm
arun@D - Thursday, April 6, 2017 1:28 PMDesNorton - Thursday, April 6, 2017 1:14 PMDoes this give you the required output?
-- This date is used so that SQL does not get an overflow error when calculating a large time diff in seconds.
DECLARE @CALC_DATE DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0);DECLARE @LOG_TIME_ORIGINAL DATETIME = '2017-04-06 14:04:00.000';
SELECT LOG_TIME_MODIFIED = DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL)
, LOG_TIME_FORMATTED = REPLACE(CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, @CALC_DATE, GETDATE()), @LOG_TIME_ORIGINAL), 108), ':', '');Not exactly....the output is in 12 HR format. But I am looking for 24 hour format.
That output is definitely in 24Hr format.
Let's try a simpler version that just gets the current minutes.
DECLARE @NOW_MINUTES INT = DATEPART(ss, GETDATE());
DECLARE @LOG_TIME_ORIGINAL DATETIME = '2017-04-06 14:04:00.000';
SELECT LOG_TIME_MODIFIED = DATEADD(ss, @NOW_MINUTES, @LOG_TIME_ORIGINAL)
, LOG_TIME_FORMATTED = REPLACE(CONVERT(VARCHAR(8), DATEADD(ss, @NOW_MINUTES, @LOG_TIME_ORIGINAL), 108), ':', '');
My output is
LOG_TIME_MODIFIED LOG_TIME_FORMATTED
----------------------- ------------------
2017-04-06 14:04:32.000 140432
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply