我该如何取整约会?
我有一张表格,其中包含我们客户的合同信息。我需要根据合约日期本身将合约到期日收集到一个月中的某一天。例如,如果合同日期是01-05-2016,我需要将其合并到01-10-2016。如果合同日期是01-11-2016,我需要将其合并到01-20-2016。最后,如果合同日期是01-21-2016,我需要将其合并到01-30-2016。这些总结日期与我们的结算周期相符,我需要我们的所有合同都属于这些结算周期之一。所有日期都是DATETIME数据类型。任何帮助,将不胜感激。我该如何取整约会?
也许这样?
DECLARE @testData TABLE(TestDate DATE);
INSERT INTO @testData VALUES({d'2016-02-05'}),({d'2016-02-12'}),({d'2016-02-21'});
SELECT TestDate
,CASE WHEN DAY(TestDate) BEtWEEN 1 AND 10 THEN 1
WHEN DAY(TestDate) BEtWEEN 11 AND 20 THEN 2
ELSE 3 END AS BillingCycle
,CASE WHEN DAY(TestDate) BEtWEEN 1 AND 10 THEN CAST(CAST(YEAR(TestDate) AS CHAR(4))+REPLACE(STR(MONTH(TestDate),2),' ','0')+'01' AS DATE)
WHEN DAY(TestDate) BEtWEEN 11 AND 20 THEN CAST(CAST(YEAR(TestDate) AS CHAR(4))+REPLACE(STR(MONTH(TestDate),2),' ','0') +'11' AS DATE)
ELSE CAST(CAST(YEAR(TestDate) AS CHAR(4))+REPLACE(STR(MONTH(TestDate),2),' ','0')+'28' AS DATE) END AS BillingCycleDate
FROM @testData
结果:
TestDate BillingCycle BillingCycleDate
2016-02-05 1 2016-02-01
2016-02-12 2 2016-02-11
2016-02-21 3 2016-02-28
我犯了一个错误。记帐周期3将始终是每个月的第28天。这就是我们如何避免这个问题。 – user3712737
@ user3712737这使得它更简单!看我的编辑! – Shnugo
谢谢!这就是诀窍..... – user3712737
你会做的更好,以避免串铸造日期。虽然我确信它可以缩短和混淆,但漫长的道路是这样的。您没有指定什么用的28后虽然日期做:
dateadd(
day,
case day(contract_dt)
when 1 then 9
when 2 then 8
...
when 10 then 0
when 11 then 9
when 12 then 8
...
when 20 then 0
when 21 then 7
when 22 then 6
...
when 28 then 0
when 29 then -1
when 30 then -2
when 31 then -3
end,
contract_dt
)
这里是更紧凑的形式我提到一个:
dateadd(day, case
when day(contract_dt) <= 20
then 10 - day(contract_dt) % 10
else 28 - day(contract_dt)
end, contract_dt)
编辑:我相信基于您接受另一个答案,你想回落到28号的异常值,所以我编辑了上述相应。
好的。由于你的帮助,我已经得到了我的合同日期和正确的日期,现在我需要在下一个付款到期日期完成同样的事情。我遇到的问题是这是一个计算字段。我创建了一个CTE来计算下一个付款到期日,但是当我尝试应用您的修补程序来整理日期时,我得到“转换日期和/或时间从字符串转换失败”。的CTE计算下一个付款到期日期如下: – user3712737
cteNextDueDate(DocNmbr,NextDueDate) AS (SELECT dbo.RM20101.DOCNUMBR, CONVERT(VARCHAR(20),DATEADD(月, - ((RM20101.CURTRXAM /(CONVERT (MONEY,CVS.PmtAmt)))-1),RM20101.DUEDATE),111)AS NextDueDate FROM dbo.RM20101 INNER JOIN dbo.SOP30200 ON dbo.RM20101.DOCNUMBR = dbo.SOP30200.SOPNUMBE LEFT OUTER JOIN dbo .SY90000 ON dbo.SY90000.ObjectID = SOP30200.SOPNUMBE AND dbo.SY90000.ObjectType ='SalesPaymentTermsEntry'AND dbo.SY90000.PropertyName ='MonthlyPayments' LEFT OUTER JOIN ctePaymentsRemaining AS CVS ON dbo.SY90000.ObjectID = CVS.ObjectID) – user3712737
四舍五入时,我使用以下代码: – user3712737
从这个月的10号开始,它们总是使用10天的增量吗? 2月份的最后一个合同日期是多久? – PinnyM
那么2016年2月21日的规则是什么? 2/28?或2/29?关于1/31/2016? 2/10?听起来像是日历表的绝佳机会。 –
用DATEADD()和DATEDIFF()来做。 –