April 21, 2015 at 8:16 am
My table as data as follow,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[table_Data](
[idx] [int] IDENTITY(1,1) NOT NULL,
[crtDte] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET IDENTITY_INSERT [dbo].[table_Data] ON
INSERT [dbo].[table_Data] ([idx], [crtDte]) VALUES (1, CAST(0x0000A47E016AE72A AS DateTime))
INSERT [dbo].[table_Data] ([idx], [crtDte]) VALUES (2, CAST(0x0000A47F00976B26 AS DateTime))
INSERT [dbo].[table_Data] ([idx], [crtDte]) VALUES (3, CAST(0x0000A480016AE848 AS DateTime))
INSERT [dbo].[table_Data] ([idx], [crtDte]) VALUES (4, CAST(0x0000A48100AAA3DB AS DateTime))
SET IDENTITY_INSERT [dbo].[table_Data] OFF
How to return row as follow
idx| Record_Status
----------------------------------
1 3 Days Ago
2 2 Days Ago
3 1 Day Ago
4 6 Minutes Ago
5 .......
6 .......
Please help
April 21, 2015 at 2:18 pm
Go to Books Online and read up on the DatePart, DateDiff and DateAdd functions and how to use them to take apart DATETIME columns.
Also look at Lynn Pettis' excellent summary of how to use them: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
Good luck,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
April 21, 2015 at 3:47 pm
This what you are looking for?
DECLARE @Today datetime
SELECT @Today = getdate()
SELECT idx,
CASE
--1 or more days
WHEN DATEDIFF(mi,crtDte, @Today) >= 1440 THEN CONVERT(varchar(15), DATEDIFF(mi,crtDte, @Today) / 1440 ) +' days '+
REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 1440) / 60 ))) + convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 1440) / 60 ) + ' hours '+
REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)))) + convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)) +' minutes ago'
-- Less than 1 day AND >= 1 hour
WHEN DATEDIFF(mi,crtDte, @Today) >= 60 and DATEDIFF(mi,crtDte, @Today) < 1440 THEN
REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 1440) / 60 ))) + convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 1440) / 60 ) + ' hours '+
REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)))) + convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)) +' minutes ago'
-- Less than 1 hour AND >= 1 minute
WHEN DATEDIFF(mi,crtDte, @Today) >= 1 and DATEDIFF(mi,crtDte, @Today) < 60 THEN
REPLICATE('0', 2 - DATALENGTH(convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)))) + convert(varchar(2), (DATEDIFF(mi,crtDte, @Today) % 60)) +' minutes ago'
ELSE '< 1 minute ago'
END as HowLongAgo
FROM table_Data
A.J.
DBA with an attitude
April 21, 2015 at 6:33 pm
Alternate approach:
CREATE TABLE #table_Data(
[idx] [int] IDENTITY(1,1) NOT NULL,
[crtDte] [datetime] NOT NULL
) ON [PRIMARY];
DECLARE @StartDT DATETIME = GETDATE();
INSERT INTO #table_Data
VALUES (@StartDT-3),(@StartDT-1)
,(DATEADD(minute, -10, @StartDT))
,(DATEADD(minute, -1, @StartDT))
,(DATEADD(second, -6, @StartDT))
,(DATEADD(second, -1, @StartDT));
SELECT idx, crtDte, Record_status=
CASE DaysAgo
WHEN 0
THEN CASE MinutesAgo
WHEN 0
THEN CASE SecondsAgo
WHEN 1
THEN '1 second ago'
ELSE CAST(SecondsAgo AS VARCHAR(20)) + ' seconds ago'
END
WHEN 1
THEN '1 minute ago'
ELSE CAST(MinutesAgo AS VARCHAR(20)) + ' minutes ago'
END
WHEN 1
THEN '1 day ago'
ELSE CAST(DaysAgo AS VARCHAR(20)) + ' days ago'
END
FROM #table_Data a
CROSS APPLY
(
SELECT DaysAgo=DATEDIFF(day, crtDte, @StartDT)
,MinutesAgo=DATEDIFF(minute, crtDte, @StartDT)
,SecondsAgo=DATEDIFF(second, crtDte, @StartDT)
) b;
GO
DROP TABLE #table_Data;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 22, 2015 at 3:22 am
dwain.c (4/21/2015)
Alternate approach:
Nice solution. And easily extendable to milliseconds or years.
May i suggest a little tweak
CREATE TABLE #table_Data(
[idx] [int] IDENTITY(1,1) NOT NULL,
[crtDte] [datetime] NOT NULL
) ON [PRIMARY];
DECLARE @StartDT DATETIME = GETDATE();
INSERT INTO #table_Data
VALUES (@StartDT-3),(@StartDT-1)
,(DATEADD(minute, -10, @StartDT))
,(DATEADD(minute, -1, @StartDT))
,(DATEADD(second, -6, @StartDT))
,(DATEADD(second, -1, @StartDT))
,(@StartDT);
SELECT idx, crtDte, Record_status=
CASE DaysAgo
WHEN 0
THEN CASE MinutesAgo
WHEN 0
THEN CASE SecondsAgo
WHEN 0
THEN 'Just now!'
ELSE CAST(SecondsAgo AS VARCHAR(20)) + ' second' + right('s',SecondsAgo-1) + ' ago'
END
ELSE CAST(MinutesAgo AS VARCHAR(20)) + ' minute' + right('s',MinutesAgo-1) + ' ago'
END
ELSE CAST(DaysAgo AS VARCHAR(20)) + ' day' + right('s',DaysAgo-1) + ' ago'
END
FROM #table_Data a
CROSS APPLY
(
SELECT DaysAgo=DATEDIFF(day, crtDte, @StartDT)
,MinutesAgo=DATEDIFF(minute, crtDte, @StartDT)
,SecondsAgo=DATEDIFF(second, crtDte, @StartDT)
) b;
GO
DROP TABLE #table_Data;
April 22, 2015 at 3:49 am
serg-52 (4/22/2015)
Nice solution. And easily extendable to milliseconds or years.May i suggest a little tweak
Months and hours too!
Of course, please tweak away. It was just a little something I threw together.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 22, 2015 at 11:13 am
SELECT
crtdte,
ISNULL(CAST(NULLIF(minutes_ago / 1440, 0) AS varchar(5)) + ' days, ', '') +
ISNULL(CAST(NULLIF(minutes_ago % 1440 / 60, 0) AS varchar(2)) + ' hours, ', '') +
CAST(minutes_ago % 60 AS varchar(2)) + ' minutes ago.' AS how_long_ago
FROM table_data
CROSS APPLY (
--I rounded mins up -- if you prefer to truncate, remove the "+ 30"
SELECT (DATEDIFF(SECOND, crtDte, GETDATE()) + 30) / 60 AS minutes_ago
) AS assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply