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)
精彩评论