Need help on query - DATEDIFF

Question

Post reply

Need help on query - DATEDIFF

Adelia

SSC-Addicted

Points: 422

December 3, 2023 at 12:58 am

Go to Answer

#4338720

Hello,

I want to have value

Month ago
Year ago

So far, I've this

   SELECT 
      CASE 
        WHEN DATEDIFF(SECOND, crtDte, GETDATE()) < 60 THEN ' Just Posted' 
        WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, crtDte, GETDATE()) AS VARCHAR(10)) + ' Minutes'
        WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, crtDte, GETDATE())/60) AS VARCHAR(10)) + ' Hours'
        ELSE CAST(FLOOR(DATEDIFF(HOUR, crtDte, GETDATE())/24) AS VARCHAR(10)) + ' Days'
    END AS Postedon 

from [dbo].[Apps_LogActivity]

order by refno desc ;

I've table and data as following,

GO
/****** Object:  Table [dbo].[Apps_LogActivity]    Script Date: 3/12/2023 8:54:49 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Apps_LogActivity](
[RefNo] [int] IDENTITY(1,1) NOT NULL,
[UserLogin_RefNo] [int] NOT NULL,
[UserLogin_UserId] [varchar](200) NULL,
[CrtDte] [datetime] NULL,
[LogDescrp] [varchar](200) NULL,
 CONSTRAINT [PK_Apps_LogActivity] PRIMARY KEY CLUSTERED 
(
[RefNo] 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 IDENTITY_INSERT [dbo].[Apps_LogActivity] ON 
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (1, 1, N'root', CAST(N'2023-12-03T05:17:46.227' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (2, 1, N'root', CAST(N'2023-12-03T05:18:47.890' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (3, 1, N'root', CAST(N'2023-12-03T05:28:29.837' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (4, 1, N'root', CAST(N'2023-12-03T05:53:23.813' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (5, 1, N'root', CAST(N'2023-12-03T05:55:23.960' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (6, 1, N'root', CAST(N'2023-12-03T06:03:52.677' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (7, 1, N'root', CAST(N'2023-12-03T06:04:23.607' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (8, 1, N'root', CAST(N'2023-12-03T06:10:43.200' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (9, 1, N'root', CAST(N'2023-12-03T06:11:16.060' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (10, 1, N'root', CAST(N'2023-12-03T06:11:42.170' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (11, 1, N'root', CAST(N'2023-12-03T06:13:06.060' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (12, 1, N'root', CAST(N'2023-12-03T06:38:52.660' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (13, 1, N'root', CAST(N'2023-12-03T06:39:05.957' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (14, 1, N'root', CAST(N'2023-12-03T06:39:14.307' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (15, 1, N'root', CAST(N'2023-12-03T06:39:38.463' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (16, 1, N'root', CAST(N'2023-12-03T06:46:56.907' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (17, 1, N'root', CAST(N'2023-12-03T06:47:09.617' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (18, 1, N'root', CAST(N'2023-12-03T06:53:33.447' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (19, 1, N'root', CAST(N'2023-12-03T06:53:44.903' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (20, 1, N'root', CAST(N'2023-12-03T07:00:04.543' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (21, 1, N'root', CAST(N'2023-12-03T07:00:16.293' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (22, 1, N'root', CAST(N'2023-12-03T07:06:53.587' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (23, 1, N'root', CAST(N'2023-12-03T07:07:07.850' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (24, 1, N'root', CAST(N'2023-12-03T07:09:04.370' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (25, 1, N'root', CAST(N'2023-12-03T07:09:16.647' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (26, 1, N'root', CAST(N'2023-12-03T07:09:49.657' AS DateTime), N'Log into system - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (27, 1, N'root', CAST(N'2023-12-03T07:10:10.840' AS DateTime), N'Logout - root')
GO
INSERT [dbo].[Apps_LogActivity] ([RefNo], [UserLogin_RefNo], [UserLogin_UserId], [CrtDte], [LogDescrp]) VALUES (28, 1, N'root', CAST(N'2023-12-03T07:16:00.167' AS DateTime), N'Log into system - root')
GO
SET IDENTITY_INSERT [dbo].[Apps_LogActivity] OFF
GO
ALTER TABLE [dbo].[Apps_LogActivity] ADD  CONSTRAINT [DF_Apps_LogActivity_CrtDte]  DEFAULT (getdate()) FOR [CrtDte]
GO

I've this and got this,

   SELECT 
      CASE 
        WHEN DATEDIFF(SECOND, crtDte, GETDATE()) < 60 THEN ' Just Posted' 
        WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, crtDte, GETDATE()) AS VARCHAR(10)) + ' Minutes'
        WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, crtDte, GETDATE())/60) AS VARCHAR(10)) + ' Hours'
        ELSE CAST(FLOOR(DATEDIFF(HOUR, crtDte, GETDATE())/24) AS VARCHAR(10)) + ' Days'
    END AS Postedon 

from [dbo].[Apps_LogActivity]

order by refno desc ;


Postedon
-----------
1 Hours
1 Hours
1 Hours
1 Hours
1 Hours
1 Hours
1 Hours
1 Hours
1 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
2 Hours
3 Hours
3 Hours
3 Hours
3 Hours
3 Hours

Please help

Jonathan AC Roberts

SSCoach

Points: 18347

December 3, 2023 at 2:22 am

Answer

#4338782

SELECT 
    CASE 
        WHEN DATEDIFF(SECOND, crtDte, GETDATE()) < 60 THEN 'Just Posted' 
        WHEN DATEDIFF(MINUTE, crtDte, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, crtDte, GETDATE()) AS VARCHAR(10)) + ' Minutes'
        WHEN DATEDIFF(HOUR, crtDte, GETDATE()) < 24 THEN CAST(DATEDIFF(HOUR, crtDte, GETDATE()) AS VARCHAR(10)) + ' Hours'
        WHEN DATEDIFF(DAY, crtDte, GETDATE()) < 30 THEN CAST(DATEDIFF(DAY, crtDte, GETDATE()) AS VARCHAR(10)) + ' Days'
        WHEN DATEDIFF(MONTH, crtDte, GETDATE()) < 12 THEN CAST(DATEDIFF(MONTH, crtDte, GETDATE()) AS VARCHAR(10)) + ' Months'
        ELSE CAST(DATEDIFF(YEAR, crtDte, GETDATE()) AS VARCHAR(10)) + ' Years'
    END AS Postedon 
FROM [dbo].[Apps_LogActivity]
ORDER BY refno DESC;

December 13, 2023 at 8:34 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adelia SSC-Addicted Points: 422 More actions · Answer 1

Adelia

SSC-Addicted

Points: 422

December 3, 2023 at 4:56 am

#4338832

Thank you

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

Not sure how long you're planning to have this code active but it will "only" continue to work if the dates are less than 68 years, 1 month, 2o days, 3 hours, 14 minutes, and 8 seconds apart 😀 😀 :D. After that, the DATEDIFF(SECONDS) will fail.

--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)