How to use Select Case in SQL with datetime field

Question

I have a table utblAdvertise and one of the field is PublishedDate and which Contains Datetime data like this

      PublishedDate
     2014-03-21 15:07:22.173
     2014-02-11 15:05:22.223 
     2014-03-21 15:15:22.673
     2014-01-01 15:15:22.272
     2014-02-11 15:15:22.173
     2014-03-20 15:15:22.372
     2014-03-26 15:15:22.393
     2014-02-25 15:15:22.273

I want the time only in case published date is between 24 hours eg. 5 Hours ago, 15 Hours ago etc. else I want to show date eg. 11-Feb-2014 I have tried for time my query is as follows

       Select PublishedDate,RIGHT(CONVERT(VARCHAR, PublishedDate, 100),7) as Time From CLF.utblAdvertise

which give the result time like 3:09PM, 11:27AM.

I want the output as

     3 hours ago -- Incase in between 24 hours
     12-Feb-2014 -- incase in not between 24 hours

any help are surely appretiated.

Not an answer, I think you should do the formatting on the front-end (say c# or java) than doing it in the backend (db). Take a look at http://stackoverflow.com/questions/11/how-do-i-calculate-relative-time — shahkalpesh, Mar 22 '14 at 07:19

score 0 · Answer 1 · answered Mar 22 '14 at 07:26

SELECT 
  CASE WHEN DATEDIFF(HOUR, PublishedDate, GETDATE()) < 24 THEN 
    CASE DATEDIFF(HOUR, PublishedDate, GETDATE()) 
    WHEN 1 THEN CONVERT(VARCHAR, DATEDIFF(HOUR, PublishedDate, GETDATE())) + ' hour ago'
    ELSE CONVERT(VARCHAR, DATEDIFF(HOUR, PublishedDate, GETDATE())) + ' hours ago'
    END
  ELSE REPLACE(CONVERT(VARCHAR, PublishedDate, 6), ' ', '-')
  END
FROM Something

How to use Select Case in SQL with datetime field

1 Answers1