adding hh:mm:ss time to yyyy-mm-dd hh:mm:ss format
-
I am trying, and this is just a start I've made, to cast and add hh:mm:ss time to yyyy-mm-dd hh:mm:ss format. I realize I may have to declare some variables to get this to work and I'm sure I'll have to explain this more especially since 'DATEADD([created_at] + [event_sec])' needs much work:
INSERT INTO [portman].[dbo].[AgentHours]
([DOMAIN]
,[LOGIN TIMESTAMP]
,[LOGIN TIME]
,[LOGOUT TIMESTAMP]
)
SELECT
'EVQ' --literal
,cast([created_at] as datetime) --yyyy-mm-dd hh:mm:ss format
,cast([event_sec] as time) --hh:mm:ss format
,DATEADD([created_at] + [event_sec]) --yyyy-mm-dd hh:mm:ss format
FROM [Portman].[dbo].[ConvoHrs]- This topic was modified 3 years ago by DaveBriCam.
-
The way I would do it is similar to this:
DECLARE @test1 DATE = GETDATE()
DECLARE @test2 TIME = GETDATE()
SELECT @test1, @test2, CAST(CAST(@test1 AS VARCHAR(255)) +' '+ CAST(@test2 AS VARCHAR(255)) AS DATETIME2)If you need it as DATETIME instead of DATETIME2, you can cast that DATETIME2 value as a DATETIME. You can't cast it immediately to DATETIME due to TIME having too much precision.
I imagine there are better ways; but this method does work. With your above example, you would cast Created_at to a DATE then follow the above example.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
You shouldn't need to declare any variables if you're pulling it out of a table, you'll just need to do some dateadds/string manipulation.
DECLARE @timestamp varchar(30) = '2021-11-01 12:32:18'
DECLARE @time varchar(30) = '03:05:12'
SELECT DATEADD(hour, CAST(LEFT(@time, 2) AS int), CAST(@timestamp AS datetime)) -
I think we may be on the right track but my data isn't presenting correctly. I'm getting (although the seconds are not visible, they are incorrect):
Logout Timestamp needs to be Login Timestamp with Login Time added.
INSERT INTO [portman].[dbo].[AgentHours]
([DOMAIN]
,[DATE]
,[LOGIN TIMESTAMP]
,[LOGIN TIME]
,[LOGOUT TIMESTAMP]
)
SELECT
'EVQ' --literal
,cast([created_at] as datetime)
,cast([created_at] as datetime)
,cast([event_sec] as time)
,DATEADD(hour, CAST(LEFT([event_sec], 2) AS int), CAST([created_at] AS datetime))
FROM [portman].[dbo].[ConvoHrs] -
Can you provide DDL and sample data for dbo.ConvoHrs, rather than asking people to use guesswork?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
How is event_sec stored in the database? Is it stored as an actual time - or is it stored as a duration? If stored as a duration - then all you need to do is add the duration to get the logout timestamp, and add the duration to midnight and cast as time:
Select 'EVQ'
, event_date = cast(created_at as date)
, [login timestamp] = created_at --should already be datetime, no need to cast
, [logout timestamp] = dateadd(second, event_sec, created_at) -- just add the seconds
, [login time] = cast(dateadd(second, event_sec, '00:00:00') as time)
FROM [portman].[dbo].[ConvoHrs]If event_sec is stored as a string - then you need to convert it to a duration, then you can add that duration to created_at to arrive at the logout timestamp:
Select 'EVQ'
, event_date = cast(created_at as date)
, [login timestamp] = created_at --should already be datetime, no need to cast
, [logout timestamp] = dateadd(second, datediff(second, '00:00:00', cast(event_sec as time), created_at) -- convert to seconds, then add
, [login time] = cast(event_sec as time)
FROM [portman].[dbo].[ConvoHrs]If event_sec is already stored as a time - then you can eliminate the cast to time.
It all depends on the actual data types in the source table - so please help us help you and provide that information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - DaveBriCam wrote:
I think we may be on the right track but my data isn't presenting correctly. I'm getting (although the seconds are not visible, they are incorrect):
Logout Timestamp needs to be Login Timestamp with Login Time added.
INSERT INTO [portman].[dbo].[AgentHours]
([DOMAIN]
,[DATE]
,[LOGIN TIMESTAMP]
,[LOGIN TIME]
,[LOGOUT TIMESTAMP]
)
SELECT
'EVQ' --literal
,cast([created_at] as datetime)
,cast([created_at] as datetime)
,cast([event_sec] as time)
,DATEADD(hour, CAST(LEFT([event_sec], 2) AS int), CAST([created_at] AS datetime))
FROM [portman].[dbo].[ConvoHrs]We've been through this before. Post the data in a readily consumable format and remember that the article at the first link in my signature line below demonstrates one of many ways to do such a thing. It'll help us help you instead of post a graphics where, in your own words, the seconds are missing.
--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) - DaveBriCam wrote:
I think we may be on the right track but my data isn't presenting correctly. I'm getting (although the seconds are not visible, they are incorrect):
Logout Timestamp needs to be Login Timestamp with Login Time added.
INSERT INTO [portman].[dbo].[AgentHours]
([DOMAIN]
,[DATE]
,[LOGIN TIMESTAMP]
,[LOGIN TIME]
,[LOGOUT TIMESTAMP]
)
SELECT
'EVQ' --literal
,cast([created_at] as datetime)
,cast([created_at] as datetime)
,cast([event_sec] as time)
,DATEADD(hour, CAST(LEFT([event_sec], 2) AS int), CAST([created_at] AS datetime))
FROM [portman].[dbo].[ConvoHrs]I agree with Jeff here - we need to know the source datatypes. That being said, I would HOPE that you are storing the "created_at" and "event_sec" as DATETIME and TIME respectively.
That being said, I think that Jeffrey Williams's answer should work for you. I see no reason why that solution won't work anyways.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
-- here are the basic DDLs for my two tables:
CREATE TABLE [portman].[dbo].[AgentHours](
[LoginEntryID] [int] IDENTITY(1,1) NOT NULL,
[DOMAIN] [varchar](4) NOT NULL,
[DATE] [date] NOT NULL,
[LOGIN TIMESTAMP] [datetime2](0) NULL,
[LOGOUT TIMESTAMP] [datetime2](0) NULL,
[LOGIN TIME] [time](0) NULL
CREATE TABLE [portman].[dbo].[ConvoHrs](
[created_at] [varchar](200) NULL,
[event_sec] [varchar](200) NULL -
On the table ConvoHrs - why are both columns defined as varchar(200)? If you can have anything put into those columns - then that is what will happen and you will then need some validation code to verify the format of the data matches.
Much easier to assign the appropriate data types.
Since event_sec is a string - what is the format of that string? Is it HH:MM:SS - or something else? If it is HH:MM:SS - you do realize that you are now limited to 24 hours, unless you write additional code to parse the values.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - DaveBriCam wrote:
-- here are the basic DDLs for my two tables:
CREATE TABLE [portman].[dbo].[AgentHours](
[LoginEntryID] [int] IDENTITY(1,1) NOT NULL,
[DOMAIN] [varchar](4) NOT NULL,
[DATE] [date] NOT NULL,
[LOGIN TIMESTAMP] [datetime2](0) NULL,
[LOGOUT TIMESTAMP] [datetime2](0) NULL,
[LOGIN TIME] [time](0) NULL
CREATE TABLE [portman].[dbo].[ConvoHrs](
[created_at] [varchar](200) NULL,
[event_sec] [varchar](200) NULLThat helps but you forgot the "readily consumable data".
Again, please read and heed the article at the first link in my signature line below. It'll really help you get high quality answers.
--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) -
The ConvoHrs table is coming from a vendor we have no control over.
-
What format would you prefer the data?
-
We have links in our signatures that show how to do that, but simply put:
Insert Into someTable (col1, col2)
Values (val1, val2), (val3, val4), (val5, val6), ...Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - DaveBriCam wrote:
What format would you prefer the data?
READ THE ARTICLE AND YOU'LL KNOW AS LEAST ONE WAY!
--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)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply