返回不同的日期

发布于 2024-12-18 08:01:07 字数 810 浏览 1 评论 0原文

早上，

我试图通过唯一的标识符返回结果的不同日期。

例如：

ID|Date
1 | 2011-10-01 23:57:59
1 | 2011-10-01 23:58:59
1 | 2010-10-01 23:59:59
2 | 2010-09-01 23:59:59
2 | 2010-09-01 23:58:29
3 | 2010-09-01 23:58:39
3 | 2010-10-01 23:59:14
3 | 2010-10-01 23:59:36

时间并不重要，日期才重要。例如，在 ID 1 上，我无法对 ID 执行不同操作，因为这只会返回我的日期之一。所以我想返回：

1|2011-10-01
1|2010-10-01

我已经尝试了以下查询：

Drop Table #Temp

select Distinct DateAdd(dd, DateDiff(DD,0, Date),0) as DateOnly
,ID
Into #Temp
From Table

Select Distinct (Date)
,ID
From #Temp

但是我得到了以下结果：

ID|Date
1 | 2011-10-01 00:00:00
1 | 2011-10-01 00:00:00
1 | 2010-10-01 00:00:00

我是 SQL 新手，所以很抱歉我可能犯了一个明显的错误。到目前为止，我已经通过搜索之前提出的问题得到了答案。

一如既往，我们非常感谢任何帮助和指示。

原文

Morning

I am trying to return the distinct dates of an outcome by a unique identifer.

For example:

ID|Date
1 | 2011-10-01 23:57:59
1 | 2011-10-01 23:58:59
1 | 2010-10-01 23:59:59
2 | 2010-09-01 23:59:59
2 | 2010-09-01 23:58:29
3 | 2010-09-01 23:58:39
3 | 2010-10-01 23:59:14
3 | 2010-10-01 23:59:36

The times are not important just the dates. So for example on ID 1 I can't do a distinct on the ID as that would return only one of my dates. So I would want to return:

1|2011-10-01
1|2010-10-01

I Have tried the following query:

Drop Table #Temp

select Distinct DateAdd(dd, DateDiff(DD,0, Date),0) as DateOnly
,ID
Into #Temp
From Table

Select Distinct (Date)
,ID
From #Temp

I am getting the following results however:

ID|Date
1 | 2011-10-01 00:00:00
1 | 2011-10-01 00:00:00
1 | 2010-10-01 00:00:00

I'm new to SQL so apologies I may have made a glaring mistake. I have got so far by searching through the previously asked questions.

As always any help and pointers is greatly appreciated.

分享到QQ

分享到微博

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

发布评论

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

乖乖公主 2024-12-25 08:01:08

您可以使用 T-SQL convert 函数来提取日期。

尝试

CONVERT(char(10), GetDate(),126)

这样做，根据您的情况，做

Drop Table #Temp

select Distinct CONVERT(char(10), DateAdd(dd, DateDiff(DD,0, Date),0), 126) as DateOnly
,ID
Into #Temp
From Table

Select Distinct (Date)
,ID
From #Temp

更多信息：获取 SQL Server 的日期部分日期时间字段

希望这有帮助！

You can use the T-SQL convert function to extract the Date.

Try

CONVERT(char(10), GetDate(),126)

so, in your case, do

Drop Table #Temp

select Distinct CONVERT(char(10), DateAdd(dd, DateDiff(DD,0, Date),0), 126) as DateOnly
,ID
Into #Temp
From Table

Select Distinct (Date)
,ID
From #Temp

further informations: Getting the Date Portion of a SQL Server Datetime field

hope this helps!

回复收藏 0 原文

花伊自在美 2024-12-25 08:01:08

如果您使用的是 Sql Server 2008 - 您可以将 DateTime 列转换为内置 Date 类型，否则要删除时间，您应该仅转换为 VARCHAR() day/月/年部分，然后转换回日期时间，因此时间部分将被归零：

declare @dates table(id int, dt datetime)
INSERT INTO @dates VALUES(1, '2011-10-01 23:57:49')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:59')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:39')

SELECT stripped.id, stripped.dateOnly 
FROM
(
   -- this will return dates with zeroed time part 2011-10-01 00:00:00.000
   SELECT id,
          CONVERT(datetime,
          CAST(YEAR(dt) as VARCHAR(4)) + '-' + 
          CAST(MONTH(dt) AS VARCHAR(2)) + '-' + 
          CAST(DAY(dt) AS VARCHAR(2))) as dateOnly
   FROM @dates
) stripped
GROUP BY stripped.id, stripped.dateOnly

If you are using Sql Server 2008 - you can cast DateTime column to a built in Date type , otherwise to get rid of time you should cast to VARCHAR() only day/month/year parts and then convert back to datetime so time part would be zeroed:

declare @dates table(id int, dt datetime)
INSERT INTO @dates VALUES(1, '2011-10-01 23:57:49')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:59')
INSERT INTO @dates VALUES(2, '2011-10-02 23:57:39')

SELECT stripped.id, stripped.dateOnly 
FROM
(
   -- this will return dates with zeroed time part 2011-10-01 00:00:00.000
   SELECT id,
          CONVERT(datetime,
          CAST(YEAR(dt) as VARCHAR(4)) + '-' + 
          CAST(MONTH(dt) AS VARCHAR(2)) + '-' + 
          CAST(DAY(dt) AS VARCHAR(2))) as dateOnly
   FROM @dates
) stripped
GROUP BY stripped.id, stripped.dateOnly

回复收藏 0 原文

~没有更多了~