SQl服务器日期差异在月份中关闭 [英] SQl Server Date Diff in month round off down
本文介绍了SQl服务器日期差异在月份中关闭的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我想通过SQL查询来查找两个日期之间的月份差异。
我需要围绕一个月下来
我需要通用查询才能完成这项任务。
我只想要一个月的时间,因为我给出了开始日期和结束日期。
但是主要目标是将月份四舍五入为下限
如果结果为1.9则结果需要更改为1
请帮我解决这个问题
谢谢
Mohan
解决方案
您好,试试这个:
CREATE TABLE #d_diffs(StartDate DATE ,EndDate DATE 跨度>);
INSERT INTO #d_diffs
VALUES (' 2014-01-01', 2014-01-31'),(' < span class =code-string> 2014-01-01',' 2014-03-01 '),
(' 2014-01-01',' 2014-03-15'),(' 2014-01-01',' 2014-03 -31')
SELECT DATEDIFF(dd,StartDate,EndDate) AS DateDiffs,
CASE WHEN DATEDIFF(dd,StartDate,EndDate) BETWEEN 31 AND 59
那么 1
WHEN DATEDIFF(dd,StartDate,EndDate) BETWEEN 60 AND 89
那么 2
- 添加任意数量的条件
ELSE 0 END AS TotalMonths
FROM #d_diffs
DROP TABLE #d_diffs
更新。
尝试以下查询:
CREATE TABLE #d_diffs(StartDate DATE ,EndDate DATE );
INSERT INTO #d_diffs
VALUES (' 2014-01-01', 2014-01-31'),(' < span class =code-string> 2014-01-01',' 2014-03-01 '),
(' 2014-01-01',' 2014-03-08'),(' 2014-01-01',' 2014-03 -31'),
(' 2014-01-01', ' 2014-04-01'),(' 2014-01-01',' 2014-04-18'),
(' 2014-01- 01',' 2014-04-30'),(' 2014-01-01',' 2014-05-01')
SELECT StartDate,EndDate,DATEDIFF(dd,StartDate, EndDate) AS DateDiffs,
CASE WHEN DATEDIFF(dd,EOMONTH(StartDate),EOMONTH(EndDate))= 0 那么 0
ELSE DATEDIFF(mm,StartDate,EndDate) - 1 END AS TotalMonths
FROM #d_diffs
DROP TABLE #d_diffs
Hi All,
I want sql query to find the difference of months between two dates.
I need to round of the month to down
I need generalized query to achive this task.
I want only month as a result when i gave start date and end date.
But main objective is month to be rounded of to down that is
if result is 1.9 then result need to change as 1
Please help me to resolve this
Thanks
Mohan
解决方案
Hello, try this one:
CREATE TABLE #d_diffs (StartDate DATE, EndDate DATE); INSERT INTO #d_diffs VALUES ('2014-01-01', '2014-01-31'), ('2014-01-01', '2014-03-01'), ('2014-01-01', '2014-03-15'), ('2014-01-01', '2014-03-31') SELECT DATEDIFF(dd, StartDate, EndDate) AS DateDiffs, CASE WHEN DATEDIFF(dd, StartDate, EndDate) BETWEEN 31 AND 59 THEN 1 WHEN DATEDIFF(dd, StartDate, EndDate) BETWEEN 60 AND 89 THEN 2 -- Add as many conditions as you wish ELSE 0 END AS TotalMonths FROM #d_diffs DROP TABLE #d_diffs
upd.
Try the following query:
CREATE TABLE #d_diffs (StartDate DATE, EndDate DATE); INSERT INTO #d_diffs VALUES ('2014-01-01', '2014-01-31'), ('2014-01-01', '2014-03-01'), ('2014-01-01', '2014-03-08'), ('2014-01-01', '2014-03-31'), ('2014-01-01', '2014-04-01'), ('2014-01-01', '2014-04-18'), ('2014-01-01', '2014-04-30'), ('2014-01-01', '2014-05-01') SELECT StartDate, EndDate, DATEDIFF(dd, StartDate, EndDate) AS DateDiffs, CASE WHEN DATEDIFF(dd, EOMONTH(StartDate), EOMONTH(EndDate)) = 0 THEN 0 ELSE DATEDIFF(mm, StartDate, EndDate) - 1 END AS TotalMonths FROM #d_diffs DROP TABLE #d_diffs
这篇关于SQl服务器日期差异在月份中关闭的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文