May 8, 2007 at 9:50 am
In my table I have a date column. It is keeping track of when someone posted a comment.
In a query how do I use custom date formatting?
So instead of the result being: 5/7/2007 10:49:03 I would like it to be in the format of "xx time ago".
Examples:
2 minutes ago
1 hour ago
2 days ago
Thank you for your assistance.
Norbert
May 8, 2007 at 9:53 am
Check out the datadiff function. Then decide how you want to present the data and go from there.
May 8, 2007 at 10:42 pm
So to get how many minutes ago the post was, you'd use something like this:
DATEDIFF(minute, @dateOfPost, GETDATE())
May 8, 2007 at 11:22 pm
Thank you both for the answer.
How do I determine the time that has passed and display a response accordingly?
For example:
if less than 1 hour show minutes
if less than 24 hours show hours
else show days
Thank you,
Norbert
May 8, 2007 at 11:37 pm
You can create a string, and then create if statements that check if the datediff for each unit of time you are interested in is greater than zero. If it is then append to the string that unit. You might want a counter so that you only get up to say, three units of time such as hours, minutes, seconds or months, days, hours, etc.
If you implement this in the database a good place for this would be in a user defined function that you pass the posting date to, and have it return a VARCHAR.
You could also do this in the UI with php/asp/C#/VB or whatever it is you use. This really is UI formatting you are doing so that would make some amount of sense.
May 9, 2007 at 12:05 am
I'm thinking something like this:
ALTER FUNCTION [dbo].[dateSinceString]
(
@pastDate DATETIME
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @returnString VARCHAR(100)
DECLARE @timeUnitCount INT
SET @returnString = ''
SET @timeUnitCount = 0
DECLARE @numYearsAgo INT
SET @numYearsAgo = DATEDIFF(YEAR, @pastDate, GETDATE())
IF @numYearsAgo > 0 BEGIN
SET @returnString = CONVERT(VARCHAR,@numYearsAgo)
IF @numYearsAgo = 1 BEGIN
SET @returnString = @returnString + ' year, '
END
ELSE IF @numYearsAgo > 1 BEGIN
SET @returnString = @returnString + ' years, '
END
SET @timeUnitCount = @timeUnitCount + 1
SET @pastDate = DATEADD(YEAR, @numYearsAgo, @pastDate)
END
DECLARE @numMonthsAgo INT
SET @numMonthsAgo = DATEDIFF(MONTH, @pastDate, GETDATE())
IF @numMonthsAgo > 0 BEGIN
SET @returnString = @returnString + CONVERT(VARCHAR,@numMonthsAgo)
IF @numMonthsAgo = 1 BEGIN
SET @returnString = @returnString + ' month, '
END
ELSE IF @numMonthsAgo > 1 BEGIN
SET @returnString = @returnString + ' months, '
END
SET @timeUnitCount = @timeUnitCount + 1
SET @pastDate = DATEADD(MONTH, @numMonthsAgo, @pastDate)
END
DECLARE @numDaysAgo INT
SET @numDaysAgo = DATEDIFF(DAY, @pastDate, GETDATE())
IF @numDaysAgo > 0 BEGIN
SET @returnString = @returnString + CONVERT(VARCHAR,@numDaysAgo)
IF @numDaysAgo = 1 BEGIN
SET @returnString = @returnString + ' day, '
END
ELSE IF @numDaysAgo > 1 BEGIN
SET @returnString = @returnString + ' days, '
END
SET @timeUnitCount = @timeUnitCount + 1
SET @pastDate = DATEADD(MONTH, @numDaysAgo, @pastDate)
END
RETURN LEFT(@returnString, LEN(@returnString) - 1)
END
I bet there is some more clever recursive way to do this but this should send you down the right path. You'd have to add in hours, minutes, seconds, etc. and you could use the counter to limit how many units you want to show...
May 9, 2007 at 10:19 am
NMT - This is amazing!
This is exactly what I was looking for.
Thank you very much for your time and help.
Norbert
May 10, 2007 at 6:53 am
You can use GETDATE() in a function?
Mattie
May 10, 2007 at 10:24 am
It works for me.
I am using SQL 2005 express, but I am not sure if that makes a difference.
Norbert
May 10, 2007 at 12:21 pm
No, in SQL Server 2000, GetDate() cannot be used directly inside of a UDF.
You can a achieve this through a view, though.
CREATE VIEW vGetDate AS SELECT GetDate() AS dt
GO
CREATE FUNCTION fTest()
RETURNS varchar(30)
AS
BEGIN
DECLARE @dt datetime
SELECT @dt = dt FROM vGetDate
RETURN Convert(varchar(30), @dt, 120)
END
GO
SELECT dbo.fTest()
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply