sql server 2008-如何在带有日期时间字段的sql中使用Select Case

Question

我有一个表utblAdvertise，其中一个字段是PublishedDate，它包含类似的日期时间数据

      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

我只想在发布日期介于24 hours例如5 Hours ago, 15 Hours ago etc.之间的情况下显示时间，否则我想显示日期例如11-Feb-2014我已经尝试了时间，我的查询如下

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

其给出类似于CCD_ 6的结果时间。

我想要的输出

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

任何帮助都是值得的。

Answer 1

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