SQL SERVER中日期和年份之间的差异

本文介绍了SQL SERVER中日期和年份之间的差异的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 Hello Everybody， 如何为以下条件编写存储过程 Ex： 表A ：表B： Emp_Id日期Emp_Id工资 1 2014-04-01 1 10000 1 2015 -07-30 输入为Emp_id，fromdate和todate 如果fromdate和todate属于同一个月份和年份表示没问题 插图：输入为Emp_Id = 1，fromdate ='2014-04-01'，todate ='2014-04-30' 如上图所示，我计算了4月份的总天数，并从表A中选择了日期，其中Emp_Id = 1，日期介于fromdate和todate之间Hello Everybody, How to write stored procedure for below conditionsEx:TABLE A: TABLE B:Emp_Id Date Emp_Id Salary 1 2014-04-01 1 10000 1 2015-07-30Inputs are Emp_id, fromdate and todateIf fromdate and todate fall in same month and year means no problemIllustration: Input is Emp_Id=1, fromdate='2014-04-01', todate='2014-04-30' For above illustration,i calculated total number of days in month April and selecting date from TABLE A where Emp_Id=1 and date between fromdate and todateSELECT @Total_Days=DATEDIFF(DAY, DATEADD(DAY, 1-DAY(@fromdate),@fromdate), DATEADD(MONTH, 1, DATEADD(DAY, 1-DAY(@fromdate), @fromdate))) 选择sal_calculation =（（表B中的工资，其中Emp_Id = 1）/ @ Total_Days）*（PresentDays） 输出 ------ sal_calculation 10000（无缺席） 9657（缺席1天） 输入内容为Emp_Id = 1，fromdate ='2014-04-01'，todate ='2015- 07-30' 如何计算工资 OUTPUT应为 ------ sal_calculation 150000（差不多15个月） - 如何获得第二个结果：工资/月总天数应该一直保持增加直到达到todate 感谢提前Select sal_calculation=((salary from Table B where Emp_Id=1)/@Total_Days)*(PresentDays)OUTPUT------sal_calculation10000(no absent)9657(1 day absent)Incase of Input is Emp_Id=1,fromdate='2014-04-01', todate='2015-07-30'how to calculate salaryOUTPUT should be------sal_calculation150000(almost 15 months)--how to get result for second one: salary/total days of month should be keep on adding till it reaches todateTHANKS IN ADVANCE推荐答案DECLARE @Salary DECIMAL(18, 2), @FromDate DATE, @ToDate DATE;SET @Salary = 10000;SET @FromDate = '2014-04-22';SET @ToDate = '2014-05-08';WITH [Salary] ( [Date], [WorkingDays], [TotalDays]) AS ( (SELECT DATEADD(dd, 1, EOMONTH(@FromDate, -1)) AS [Date], ( CASE WHEN (@ToDate <= EOMONTH (@FromDate)) THEN (DATEDIFF(dd, @FromDate, @ToDate)) ELSE (DATEDIFF(dd, @FromDate, EOMONTH(@FromDate)) + 1) END ) AS [WorkingDays], DAY(EOMONTH(@FromDate)) AS [TotalDays] ) UNION ALL (SELECT DATEADD(mm, 1, [Date]) AS [Date], ( CASE WHEN (@ToDate <= EOMONTH (DATEADD(mm, 1, [Date]))) THEN (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), @ToDate)) ELSE (DATEDIFF(dd, (DATEADD(mm, 1, [Date])), EOMONTH(DATEADD(mm, 1, [Date]))) + 1) END ) AS [WorkingDays], DAY(EOMONTH([Date], 1)) AS [TotalDays] FROM [Salary] WHERE (MONTH([Date]) < MONTH(@ToDate)) OR (YEAR([Date]) < YEAR(@ToDate))) )SELECT CONVERT(DECIMAL(18, 2), ( SUM(@Salary * [WorkingDays] / [TotalDays]) )) AS [Salary]FROM [Salary]OPTION (MAXRECURSION 32747); 这很重要：如果您使用的是以前版本的 SQL Server （2005,2008,2008 R2），则需要使用其他功能更改 EOMONTH 功能或创建用户定义的函数，返回该月的最后一天。此外，您可以通过创建一个函数来优化它，该函数也返回该月的第一天。 那么这是如何工作的？首先，我每个月都有工作日和总天数：This is important: If you're using a previous version of SQL Server (2005, 2008, 2008 R2), you need to change the EOMONTH function with other functions or create a user defined function, which returns the last day of the month. Also, you can optimize this by creating a function, which returns the first day of the month as well.So how this works? First of all, I am getting working days and total days for each month:MonthWorkingDaysTotalDaysApril930May731 注意： @ToDate 不包括在内！然后我用这个公式计算工资总和：薪水= @Salary * [WorkingDays] / [TotalDays]。结果是 5258.06 或（（10000 * 9/30）+（10000 * 7/31））。 如果 @FromDate ='2014-04-01'和 @ToDate ='2015-07-30'，结果是 159354.84 的。最后一天（2015-07-30）不包括在内。如果包含那一天，结果将是 160000.00 （16个月）。Note: @ToDate is not included! Then I am calculating the sum of salary using this formula: Salary = @Salary * [WorkingDays] / [TotalDays]. The result is 5258.06 or ((10000 * 9 / 30) + (10000 * 7 / 31)).If @FromDate = '2014-04-01' and @ToDate = '2015-07-30', then the result is 159354.84. The last day (2015-07-30) is not included. If that day would be included, the result would be 160000.00 (16 months).select (10000/30)* datediff(day,'2014/04/01','2014/05/15') as salDECLARE @Salary DECIMAL(18, 2), @FromDate datetime, @ToDate datetime,@MonthDiff intSET @Salary = 10000;SET @FromDate = '2014-04-01';SET @ToDate = '2014-05-22';SET @MonthDiff=datediff(mm,@FromDate, @ToDate)IF @MonthDiff=0BEGIN SELECT @Salary * (datediff(dd,@FromDate, @ToDate)+1)/ day(EOMONTH(@FromDate))ENDELSEBEGIN SELECT @Salary * (datediff(dd,@FromDate, EOMONTH(@FromDate))+1)/ day(EOMONTH(@FromDate)) +@Salary * day(@ToDate)/ day(EOMONTH(@ToDate)) +@Salary * (@MonthDiff-1)END 这篇关于SQL SERVER中日期和年份之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！