如何获取日子而不是在SQL中获取所有日子 [英] How to get days instead of getting all the days in SQL

问题描述

使用以下查询我将以这种方式获得输出

Using below query i am getting output in this way

select empid, cast(floor(experience / 365) as varchar) + ' years ' +
              cast(floor(experience % 365 / 30) as varchar) + ' months ' +
              cast(experience % 30 as varchar) + ' days' as experience
from (select *, datediff(DAY, doj, getdate()) as experience
      from employee) t

但是我需要几个月和几天才能完成，但我没有得到所有的日子我只需要剩余的日子，即1到30或31



我尝试了什么：



i已经尝试过上面的代码无效

but i need years months and days which was done but instead of getting all the days i need to get only the remaining days ie between 1 to 30 or 31

What I have tried:

i had tried the above code which is not working

推荐答案

您需要减去整个月的天数。但无论如何它并不精确，因为你假设每个月有30天。我就是这样做的：

You need to subtract the number of days that is taken by the whole months. But anyway it's not precise because you assume every month has 30 days. This is how I would do it:

declare @date as date = '20150106'
declare @now as date = GetDate()
declare @years as int = DATEDIFF(year, @date, @now) -- full years
declare @date2 as date = dateadd(year, @years, @date) -- date moved by # of years
declare @months as int = DATEDIFF(month, @date2, @now) -- full months since the new date
declare @date3 as date = dateadd(month, @months, @date2) -- date moved by # of months
declare @days as int = DATEDIFF(day, @date3, @now) -- full days since the new date
select @years as [Years], @months as [Months], @days as [Days]

这是有目的的一步一步完成的，所以你可以跟踪发生的事情。例如，当我计算年数时，我按相同的时间间隔移动日期，在下一步中，我使用此修改日期。这可以很容易地内联形成一个单一的陈述，但是很难阅读。

编辑

上面的解决方案仅适用于某些的情况。它实际上不适用于2016年6月1日。以下是更新版本：

This is done step by step on purpose so you can follow what is happening. When I calculate the number of years for example I move the date by the same time interval and in next step I use this modified date. This can be easily inlined to form a single statement but would be extremely hard to read.
Edit
The solution above does only work in some situations. It actually doesn't work for 1-Jun-2016. Here is the updated version:

declare @date as date = '20150601'
declare @now as date = GetDate()
declare @yearOffset as int = case when month(@date) < month(@now) or (month(@date) = month(@now) and day(@date) <= day(@now)) then 0 else -1 end
declare @years as int = DATEDIFF(year, @date, @now) + @yearOffset
declare @date2 as date = dateadd(year, @years, @date)
declare @monthOffset as int = case when day(@date) <= day(@now) then 0 else -1 end
declare @months as int = DATEDIFF(month, @date2, @now) + @monthOffset
declare @date3 as date = dateadd(month, @months, @date2)
declare @days as int = DATEDIFF(day, @date3, @now)
select @years as [Years], @months as [Months], @days as [Days]

作为解决方案1的替代方案（但请注意，正如所警告的那样，极难阅读）：

As an alternative to Solution 1 (but note it is, as warned, extremely hard to read):

declare @date as date = '2015-06-01'

SELECT  CAST(DATEDIFF(day, @date,GETDATE()) / 365 AS varchar) + ' Years ' + 
		CAST(CAST(DATEDIFF(day, @date, GETDATE()) % 365 / 30.5 AS int) AS varchar) + ' Months ' +
		CAST(DATEDIFF(DAY, DATEADD(MONTH, CAST(DATEDIFF(day, @date, GETDATE()) % 365 / 30.5 AS int), 
				DATEADD(YEAR, CAST(DATEDIFF(day, @date,GETDATE()) / 365 AS int), @date)), GETDATE()) AS varchar) + ' Days'

原理相同，删除已计算的年份和月份的天数，以留下剩余的天数。

虽然不是一个好的方法 - 它假设所有年份都有365天，并且使用一个月中30.5的平均天数（这比使用30天稍微准确一些）。

将会具体日期这个计算是错误的。



以下给出了正确的结果，无论何时运行都应该准确。它仍然难以阅读：

Principle is the same, remove the number of days from the years and months already calculated to leave the remaining days.
This is not a good approach though - it assumes all years have 365 days and uses the "average" number of days in a month of 30.5 (which is only slightly more accurate than using 30).
There will be specific dates where this calculation is just wrong.

The following gives the correct result and should be accurate regardless of when run. It is still as difficult to read:

declare @start as date = '2015-Jun-01'
declare @end as Date = getdate()

select @start, CAST(DATEDIFF(MONTH, @start, @end) / 12 AS varchar) + ' Years ' + 
	CAST(DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(MONTH, @start, @end) / 12, @start), @end) as varchar) + ' Months ' + 
	CAST(DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, DATEADD(YEAR, DATEDIFF(MONTH, @start, @end) / 12, @start), @end), DATEADD(YEAR,DATEDIFF(MONTH, @start, @end) / 12,@start)), @end) AS varchar) + ' Days'

SELECT CAST(DATEDIFF(yy, day, GETDATE()) AS varchar(2)) + ' Years '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day), GETDATE()) AS varchar(2)) +' Month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day), GETDATE()), DATEADD(yy, DATEDIFF(yy, day, GETDATE()), day)),  GETDATE()) AS varchar(2)) +' Days ' AS Expr1
from records;

这篇关于如何获取日子而不是在SQL中获取所有日子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！