Is it possible to format date in the MSSQL procedure itself so it can be displayed as informal date?

I want the date and time to be displayed as something like this on my page :-

 1 minute ago
25 minutes ago
45 minutes ago
4开发者_如何学运维 hours ago
3 weeks ago

my stored procedure returns the date in the following format:-

2011-02-08 13:14:44.513

Can I format it the way I want it to be displayed in the SP only or do i have to do coding in the code behind of my aspx page?

The database is simply the wrong level to be formatting things. It's jobs:

• data in, data out (queries/manipulation in whatever form)
• robust persistence, integrity, atomicity, etc (ACID)
• and nothing else

sure, you could format that at the DB, but why would you? That should be done in your app-tier, allowing:

• cache and reuse of the unchanging 2011-02-08 13:14:44.513 (where-as "1 minute ago" changes constantly with time) - cache the data, etc
• internationalization and localization
• correct encoding for the UI (is it html? xml? xaml? csv? txt? winforms?) - the DB should not know about the UI
• scaling "out" rather than "up" - don't do more than you need in the DB; multiple app servers are cheaper than a ultra-powerful DB server

As for "coding in the code behind of my aspx page", well you don't need to do that either ;p (but then, I'm pretty partial to MVC+razor)

As far as I know there is no built in function to do this, however a case statement and some math can return what you want.

I agree that it should be done behind the application rather than at the database level but that being said. Here is some code I threw together as an example, you should be able to modify it for your needs.

    DECLARE @SecDifference INTEGER

    DECLARE @OutPut nvarchar(25)

    DECLARE @CheckDate DATETIME

    SET @CheckDate = '2011-03-06 06:01:58.187'


    SET @SecDifference = (SELECT DATEDIFF(ss, @CheckDate ,GETDATE()))

    SET @OutPut = (CASE WHEN @SecDifference < 61 THEN CONVERT(VARCHAR(10), @SecDifference/60) + ' minute ago' 
                WHEN @SecDifference > 60 AND @SecDIfference <= 3600 THEN CONVERT(VARCHAR(10), @SecDifference/60) + ' minutes ago' 
                WHEN @SecDifference > 3600 AND @SecDifference <= 86400 THEN  CONVERT(VARCHAR(10), @SecDifference/3600) + ' hours ago' 
                WHEN @SecDifference > 86400 AND @SecDifference <= 604800 THEN CONVERT(VARCHAR(10), @SecDifference/86400) + ' Day(s) ago' 
                WHEN @SecDifference > 604800 AND @SecDifference <= 2419200 THEN CONVERT(VARCHAR(10), @SecDifference/604800) + ' Week(s) ago' 
                WHEN @SecDifference > 2419200 AND @SecDifference < 29030400 THEN CONVERT(VARCHAR(10), @SecDifference/2419200) + ' Month(s) ago' 
                ELSE 'NOT' END)

    SELECT @OutPut
    --RETURN @OutPut

Use CAST and CONVERT (Transact-SQL). Check the available Date and Time Styles.

Using the DATEDIFF function in a series of If or CASE statements will get you what you're looking for. Though, given the choice, it would probably be better to do it in your application.

Here is a function that will give you what you're looking for:

CREATE FUNCTION GetFriendlyElapsedTimePeriod 
(
    @ElapsedDateTime DATETIME
)
RETURNS VARCHAR(50)
AS
BEGIN

DECLARE @ElapsedDateTime DATETIME, @currentDate DATETIME, @elapsed INT, @period VARCHAR(10)

SET @currentDate = GETDATE()

--USE DAY instead of WEEK so that if the day in the past is Sunday 
--and today is Monday it will return 1 day rather than 1 week
IF (DATEDIFF(DAY, @elapsedDateTime, @currentDate) > 6) 
BEGIN
    SET @period  = 'week'
    SET @elapsed = CONVERT(INT, CONVERT(DECIMAL, DATEDIFF(DAY, @elapsedDateTime, @currentDate)) / 7.0)
END
ELSE IF (DATEDIFF(DAY, @elapsedDateTime, @currentDate) > 0)
BEGIN
    SET @period  = 'day'
    SET @elapsed = DATEDIFF(DAY, @elapsedDateTime, @currentDate)
END
ELSE IF (DATEDIFF(HOUR, @elapsedDateTime, @currentDate) > 0)
BEGIN
    SET @period  = 'hour'
    SET @elapsed = DATEDIFF(HOUR, @elapsedDateTime, @currentDate)
END
ELSE IF (DATEDIFF(MINUTE, @elapsedDateTime, @currentDate) > 0)
BEGIN
    SET @period  = 'minute'
    SET @elapsed = DATEDIFF(MINUTE, @elapsedDateTime, @currentDate)
END

IF (@elapsed > 1)
BEGIN
    SET @period = @period + 's'
END 

RETURN CONVERT(VARCHAR(10), @elapsed) + ' ' + @period + ' ago'

END
GO

I'd code it in the page.

In addition to Mark G's answer, you may not call the database with caching etc on refresh because the data in the database may not change. But you still have current time and database record time in the web page (server)