i work on sql server i can't calculate created date time from every row and previous based on created date
my structure table as below :
ID ErrorMessage Line OperationName Comment CreationDate difftime
176797 Success 4044 Trade Code Generation Job ProductID: 4044 2021-11-19 08:20:21.350 0
176798 Success 4046 Trade Code Generation Job ProductID: 4046 2021-11-19 08:21:46.000 0
176799 Success 4047 Trade Code Generation Job ProductID: 4047 2021-11-19 08:21:58.350 0
176800 Success 4049 Trade Code Generation Job ProductID: 4049 2021-11-19 08:22:14.807 0
176801 Success 4050 Trade Code Generation Job ProductID: 4050 2021-11-19 08:22:22.767 0
176802 Success 4052 Trade Code Generation Job ProductID: 4052 2021-11-19 08:22:31.300 0
my data sample as below :
CREATE TABLE [dbo].[TradeCodesErrorLog](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ErrorMessage] [varchar](1000) NULL,
[Line] [int] NULL,
[OperationName] [varchar](100) NULL,
[Comment] [varchar](200) NULL,
[CreationDate] [datetime] NULL,
CONSTRAINT [PK_TradeCodesErrorLog] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
SET IDENTITY_INSERT [dbo].[TradeCodesErrorLog] ON
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176797, N'Success', 4044, N'Trade Code Generation Job', N'ProductID: 4044', CAST(0x0000ADE500896D45 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176798, N'Success', 4046, N'Trade Code Generation Job', N'ProductID: 4046', CAST(0x0000ADE50089D078 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176799, N'Success', 4047, N'Trade Code Generation Job', N'ProductID: 4047', CAST(0x0000ADE50089DEF1 AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176800, N'Success', 4049, N'Trade Code Generation Job', N'ProductID: 4049', CAST(0x0000ADE50089F23A AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176801, N'Success', 4050, N'Trade Code Generation Job', N'ProductID: 4050', CAST(0x0000ADE50089FB8E AS DateTime))
INSERT [dbo].[TradeCodesErrorLog] ([ID], [ErrorMessage], [Line], [OperationName], [Comment], [CreationDate]) VALUES (176802, N'Success', 4052, N'Trade Code Generation Job', N'ProductID: 4052', CAST(0x0000ADE5008A058E AS DateTime))
so diff time for first row as ID 176797 will be 0
diff time for second row as 176798 will be
2021-11-19 08:21:46.000
subtract or minus
2021-11-19 08:20:21.350 and result will assign to diff time to 176798
diff time for third row as 176799 will be
2021-11-19 08:21:58.350
subtract or minus
2021-11-19 08:21:46.000 and result will assign to diff time to 176799
so how to make select query display different time by minute for table tradecodeerrorlog
November 20, 2021 at 7:01 am
You have not provided the format that you need the time difference in, so I am providing it as [time].
However, this will not cater for values greater than 24 hours.
select *
, TimeDeiff = CAST(DATEADD(ms, DATEDIFF(ms, LAG(CreationDate, 1, CreationDate) OVER (ORDER BY ID), CreationDate), 0) AS time(3))
from dbo.TradeCodesErrorLog;
November 20, 2021 at 8:20 am
ok it working
remaining one thing
data display as
00:33:55.480
exactly I need to display as
33:55.480
so how to do that
November 20, 2021 at 8:59 am
The display that you are seeing is the format of the time data type. You should not be formatting the data in SQL - That is the job of the presentation layer.
If you insist on doing the formatting in SQL, you can start by looking at the following functions
November 20, 2021 at 9:11 am
i try with following
select *
, TimeDeiff = cast(right('00:',CAST(DATEADD(ms, DATEDIFF(ms, LAG(CreationDate, 1, CreationDate) OVER (ORDER BY ID), CreationDate), 0) AS time(3))) as varchar(30))
from dbo.TradeCodesErrorLog;
it give me error
Msg 206, Level 16, State 2, Line 1
Operand type clash: time is incompatible with int
Your use of the RIGHT function is incorrect.
Here are 3 different ways to achieve the format you are looking for
Using SUBSTRING
TimeDeiff = SUBSTRING(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, LAG(CreationDate, 1, CreationDate) OVER (ORDER BY ID), CreationDate), 0), 114), 4, 9)
Using RIGHT
TimeDeiff = RIGHT(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, LAG(CreationDate, 1, CreationDate) OVER (ORDER BY ID), CreationDate), 0), 114), 9)
Using STUFF
STUFF(CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, LAG(CreationDate, 1, CreationDate) OVER (ORDER BY ID), CreationDate), 0), 114), 1, 3, '')
November 24, 2021 at 7:36 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply