在日期时间上选择不同的

发布于 2024-11-14 12:09:39 字数 462 浏览 1 评论 0原文

我需要以 MMM YYYY 格式显示 SQLServer 2005 表中名为 datetimestamp 的日期时间字段的不同值。我还需要将它们按时间顺序排序。

到目前为止，我有这样的：

Select distinct CONVERT(CHAR(4), datetimestamp, 100) + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear from TableName order by MonthYear

由于转换，“order by”当然会按 alpha 顺序对其进行排序。

 Apr 2009 
 Dec 2009 
 Feb 2009

我如何让它按时间顺序排序？

Feb 2009
Apr 2009
Dec 2009

谢谢。

原文

I need to display distinct values of a datetime field called datetimestamp from a SQLServer 2005 table in the MMM YYYY format. I also need them to be sorted in chronological order.

So far I have this:

Select distinct CONVERT(CHAR(4), datetimestamp, 100) + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear from TableName order by MonthYear

The "order by" of course sorts it in alpha order due to the conversion.

 Apr 2009 
 Dec 2009 
 Feb 2009

How do I get it to sort it in chronological order?

Feb 2009
Apr 2009
Dec 2009

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

各自安好 2024-11-21 12:09:39

edit2：看起来您需要拉回不止一列。这仅使用前一次尝试中的子查询：

    select distinct DATEPART(year,datetimestamp) as year, 
           DATEPART(month,datetimestamp) as month,
           CONVERT(CHAR(4), datetimestamp, 100) 
           + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear
    from TableName order by DATEPART(year,datetimestamp), 
                            DATEPART(month,datetimestamp)

edit2: It looks like you'll need to pull back more than one column. This uses just the subquery from the previous attempt:

    select distinct DATEPART(year,datetimestamp) as year, 
           DATEPART(month,datetimestamp) as month,
           CONVERT(CHAR(4), datetimestamp, 100) 
           + CONVERT(CHAR(4), datetimestamp, 120) as MonthYear
    from TableName order by DATEPART(year,datetimestamp), 
                            DATEPART(month,datetimestamp)

回复收藏 0 原文