Post reply

Need help on query - DATEDIFF

  • December 3, 2023 at 12:58 am

    Go to Answer

    #4338720

    Hello,

    I want to have value

    1. Month ago
    2. 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

  • 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 3, 2023 at 4:56 am

    #4338832

    Thank you

  • December 4, 2023 at 2:06 am

    #4339220

    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)

  • 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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy