如何在 SQL Server 2008 中使用 T-SQL 将日转换为年月日格式？

发布于 2024-10-09 02:43:35 字数 122 浏览 0 评论 0原文

我的表中有一个 int 字段（天）。如何将其格式化为年月日字符串？

我想使用 T-SQL SQL Server 2008 执行此操作。

例如：

65 天 = 0 年 2 月 5 天

原文

分享到QQ

分享到微博

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

发布评论

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

情深已缘浅 2024-10-16 02:43:35

这里的“零”是 1900 年 1 月 1 日。这给出了与此相关的日期。

SELECT
   dateadd(day, myColumn, 0)
FROM
   myTable

如果您想单独细分组件值，

WITH CTE AS
(
  SELECT
     dateadd(day, myColumn, 0) AS theDate 
  FROM
     myTable
)
SELECT
  DATEPART(year, theDate) - 1900,
  DATEPART(month, theDate),
  DATEPART(day, theDate)
FROM
  CTE

那么您还会遇到闰年问题。 1904 年 2 月 28 日之后，就有 1904 年 2 月 29 日。没有 100% 保证的方式将“天”表示为年 + 月 + 天，因为月份长度大约每 4 年左右一次。

如果我误解了，请将其重新设定为“零”年

SELECT
   DATEADD(day, DATEDIFF(day, '0001-01-01', 0), CAST(DATEADD(day, myColumn, 1) as date))
FROM
   myTable

The "zero" here is 01 Jan 1900. This gives a date related to this

SELECT
   dateadd(day, myColumn, 0)
FROM
   myTable

If you want to break out the component values separately

WITH CTE AS
(
  SELECT
     dateadd(day, myColumn, 0) AS theDate 
  FROM
     myTable
)
SELECT
  DATEPART(year, theDate) - 1900,
  DATEPART(month, theDate),
  DATEPART(day, theDate)
FROM
  CTE

Then you also have the leap year issues. After 28 Feb 1904, you have 29 Feb 1904 There isn't a 100% guaranteed way to express "days" as years + months + days because month lengths very every 4 years or so.

In case I've misunderstood, to rebase it to year "zero"

SELECT
   DATEADD(day, DATEDIFF(day, '0001-01-01', 0), CAST(DATEADD(day, myColumn, 1) as date))
FROM
   myTable

回复收藏 0 原文

夜雨飘雪 2024-10-16 02:43:35

declare @days int
set @days = 65
select 
cast(year(dateadd(day,@days,0))-1900 as varchar) + ' Year '
+ cast(month(dateadd(day,@days,0)) as varchar) + ' Month '
+ cast(day(dateadd(day,@days,0)) as varchar) + ' Day'

请记住，这是从 junuari 1 日和其他问题开始测量的

declare @days int
set @days = 65
select 
cast(year(dateadd(day,@days,0))-1900 as varchar) + ' Year '
+ cast(month(dateadd(day,@days,0)) as varchar) + ' Month '
+ cast(day(dateadd(day,@days,0)) as varchar) + ' Day'

Keep in mind that this is measured from 1st of junuari and other problems

回复收藏 0 原文

一紙繁鸢 2024-10-16 02:43:35

我得到了这段代码...

select
cast((DATEDIFF(year, StartDate, getdate()))as CHAR(3)) +'years '+
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0 
then
cast(DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()) -1 AS CHAR(2))
else 
cast (DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())AS CHAR(2))
end +'months '+
-------------------------------------------
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0
then
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())-1,
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()) as CHar(2))
else
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE())as Char(2))
end+'days' as
Lenghtofservice
--DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)) 
 from dbo.Employee

I got to this code...

select
cast((DATEDIFF(year, StartDate, getdate()))as CHAR(3)) +'years '+
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0 
then
cast(DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()) -1 AS CHAR(2))
else 
cast (DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())AS CHAR(2))
end +'months '+
-------------------------------------------
case when (datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()))<0
then
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate())-1,
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE()) as CHar(2))
else
cast(datediff(DAY,DATEADD(month,DATEDIFF(month,DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte), getdate()),
DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)),GETDATE())as Char(2))
end+'days' as
Lenghtofservice
--DATEADD(year,(DATEDIFF(year, StartDate, getdate())),StartDAte)) 
 from dbo.Employee

回复收藏 0 原文

~没有更多了~