获取给定两个日期的天数、月数、年数并进行类型转换

发布于 2025-01-10 10:26:49 字数 802 浏览 0 评论 0原文

我试图从 2 个日期获取天数、月份和年份，但这些都是单独的

DECLARE @datecount INT;
DECLARE @monthcount INT;
DECLARE @yearcount INT;

SELECT @datecount = (SELECT DATEDIFF(DAY, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

SELECT @monthcount = (SELECT DATEDIFF(MONTH, usr.registrationdate, GETDATE())
                      FROM   users usr
                      WHERE  usr.userid = @UserId);

SELECT @yearcount = (SELECT DATEDIFF(YEAR, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

如何投射这 3 个变量？或者我怎样才能一步获得这个？

no. of days / no. of months / no. of year

我想要的输出是 = 3:1:2 或 3 天/1 个月/4 年

原文

I am trying to get the number of days,month and year from 2dates, but those are getting individual

DECLARE @datecount INT;
DECLARE @monthcount INT;
DECLARE @yearcount INT;

SELECT @datecount = (SELECT DATEDIFF(DAY, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

SELECT @monthcount = (SELECT DATEDIFF(MONTH, usr.registrationdate, GETDATE())
                      FROM   users usr
                      WHERE  usr.userid = @UserId);

SELECT @yearcount = (SELECT DATEDIFF(YEAR, usr.registrationdate, GETDATE())
                     FROM   users usr
                     WHERE  usr.userid = @UserId);

How to cast these 3 variables? Or how can I get this in one step?

no. of days / no. of months / no. of year

What I want the output is = 3:1:2 or 3 days / 1 month / 4 years

分享到QQ

分享到微博

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

发布评论

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

梦初启 2025-01-17 10:26:49

用户定义的函数对此很有用。
因为这样一个间隔的正确计算并不像人们想象的那么简单。

下面的 UDF 计算 2 个日期之间的间隔，并以 JSON 对象的格式输出。

创建函数 dbo.fnGetDateInterval
（
   @FromDate 日期，@ToDate 日期
）
返回 NVARCHAR(40)
与架构绑定
作为
开始
  声明 @Years INT、@Months INT、@Days INT；
  声明@tmpFrom DATE；
  SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
      - IIF(@ToDate < DATEADD(年, DATEDIFF(年, @FromDate, @ToDate), @FromDate), 1, 0);
  SET @tmpFrom = DATEADD(年份, @Years , @FromDate)
  SET @Months = DATEDIFF(MONTH, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(月,DATEDIFF(月, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  SET @tmpFrom = DATEADD（月，@Months，@tmpFrom）
  SET @Days = DATEDIFF(DAY, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(DAY, DATEDIFF(DAY, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  RETURN CONCAT('{', '"年":', @Years, 
                     '，“月”：'，@Months， 
                     '，“天”：'，@Days，'}'）
结尾;

声明 @UserId INT = 42;

SELECT CONCAT(天, ' 天 ', 月, ' 月 ', 年, ' 年') AS DaysMonthsYears
来自用户 usr
交叉应用 OPENJSON(dbo.fnGetDateInterval(usr.registrationdate, GETDATE())) 
            AND (years INT '$.years',
                  月 INT '$.月', 
                  天 INT '$.days') AS js
WHERE usr.userid = @UserId;

<表类=“s-表”>
<标题>
日月年年

<正文>
3天1个月4年

在 db<>fiddle 此处

A user-defined function can be useful for this.
Since the correct calculation for such an interval isn't as straight forward as one would assume.

The UDF below calculates the interval between 2 dates, with the output in the format of a JSON object.

CREATE FUNCTION dbo.fnGetDateInterval
(
   @FromDate DATE, @ToDate DATE
)
RETURNS NVARCHAR(40)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @Years INT, @Months INT, @Days INT;
  DECLARE @tmpFrom DATE;
  SET @Years = DATEDIFF(YEAR, @FromDate, @ToDate)
      - IIF(@ToDate < DATEADD(YEAR, DATEDIFF(YEAR, @FromDate, @ToDate), @FromDate), 1, 0);
  SET @tmpFrom = DATEADD(YEAR, @Years , @FromDate)
  SET @Months =  DATEDIFF(MONTH, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(MONTH,DATEDIFF(MONTH, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  SET @tmpFrom = DATEADD(MONTH, @Months , @tmpFrom)
  SET @Days =  DATEDIFF(DAY, @tmpFrom, @ToDate)
      - IIF(@ToDate < DATEADD(DAY, DATEDIFF(DAY, @tmpFrom, @ToDate), @tmpFrom), 1, 0);
  RETURN CONCAT('{', '"years":', @Years, 
                     ',"months":', @Months, 
                     ',"days":', @Days, '}')
END;

DECLARE @UserId INT = 42;

SELECT CONCAT(days, ' days ', months, ' months ', years, ' years') AS DaysMonthsYears
FROM users usr
CROSS APPLY OPENJSON(dbo.fnGetDateInterval(usr.registrationdate, GETDATE())) 
            WITH (years  INT '$.years',
                  months INT '$.months', 
                  days   INT '$.days') AS js
WHERE usr.userid = @UserId;