日期逻辑题
我正在开发一个计费系统，需要帮助，因为我在这里一无所知。我有一些客户，他们的水电费支付是我的银行的责任。在我的系统中，我正在向他们的公用事业公司注册客户以及以下数据。
**Biller Table
客户 Josh &公司
公用事业公司 A
消费者 #................................ AL12111
账单生成日期 .... 每月 07 日
账单到期日............每月18日
我的银行的截止日期.. 每月 12 日
现在在账单生成屏幕上，我正在接受如下输入。
消费者 # ...... AL12111
账单输入日期 .. 29-10-2010 （只读字段系统日期）
客户 .... Josh &公司
账单月份.... 十月
应付金额... 5,000 美元
现在我的问题是，我如何确定会计师是否在到期日之前生成账单，但在账单表中我没有月份和年份，只有日期月。
我将感谢您的帮助和解决方案建议..
谢谢
I am developing a billing system and need help as i am clueless here. I have some customers and their utility bill payment is responsibility of my bank. In my system I am registering customers with their utility company along with the following data.
**Biller Table
Customer Josh & Co.
Utility Company A
Consumer #............... AL12111
Bill Generation Date .... 07th of every month
Bill Due Date ........... 18th of every month
Cut off Date of my bank.. 12th of every month
Now on bill generation screen I am taking inputs like the following.
Consumer # ...... AL12111
Bill Entry Date.. 29-10-2010 (Readonly field System Date)
Customer .... Josh & Co.
Billing Month .... October
Amount Due ... 5,000 US$
Now my problem is that how do i figure out if accountant is generating bill before due date or not but in biller table i dont have month and year just the date of month.
I would appreciate your help and solution advise..
thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这不是一个 SQL Server 问题，甚至不是一个编程问题 - 这是一个只能由业务分析师/利益相关者回答的业务问题。
为了扩展，您有一个输入日期 yyyy-mm-dd（我们称其为 dt_entry，日期时间）和账单到期日，我们称其为 d（整数）。
您将在与输入日期相同的月份中创建一个日期（请注意，对于接近月底的日期，您将需要从业务部门获得一些关于规则的澄清 - 可能使用某种滑动窗口）。
因此，日期形式的截止日期将是 dt_entry - DAY(dt_entry) + d （SQL Server 日期时间可以使用简单的整数加法计算天数，不需要 DATEADD）。
您可以看到这会在月底附近导致问题，因为 d 会很小（比如 1），而输入日期会很大（比如 31），然后假设的到期日实际上是下个月。但是，如果一个人非常早付款（例如 25 日的到期日是 5 日）怎么办？
另外，您可能需要考虑短月份，因为 2 月不可能有 31 日到期。
所有这些都是只有业务答案的编程问题。
This isn't a SQL Server question or even a programming question - this is a business question that can only be answered by your business analyst/stakeholder.
To expand, you have a date of entry yyyy-mm-dd (lets call that dt_entry, a datetime) and a day of month which the bill is due, lets call that d (an integer).
You would manufacture a date in the same month as the date of entry (note that for dates near the end of the month, you will need to get some clarification from the business as to what the rules are - perhaps using some kind of sliding window).
So the due date in date form would be dt_entry - DAY(dt_entry) + d (SQL Server datetime can use simple integer addition for days, no need for DATEADD).
You can see where this will cause a problem near the end of the month, because d will be small (say 1), and the entry date will be large, (say 31) and then the hypothetical due date is really the next month. But what if a person is paying really early (like the 5th for a due date of the 25th)?
Also, you may need to consider the short months, since there cannot be a 31st in February on which to be due.
All these are programming problems which only have business answers.
创建过程 [sp_BillDateCheck]
@CURMONTH VARCHAR(3),
@MONTHNUM INT 输出，
@EBDATEMATCH VARCHAR(90) 输出
AS
开始
声明@ENTRYDATE日期时间
SELECT @ENTRYDATE = GETDATE() --获取当前输入日期
--通过给出月份名称获取月份
SELECT @MONTHNUM = DATEPART(mm,CAST(@CURMONTH + ' 1900' AS DATETIME))
IF DATEPART(MONTH, @ENTRYDATE) = @MONTHNUM
SELECT @EBDATEMATCH = '输入日期和帐单月份相同'
IF DATEPART(MONTH, @ENTRYDATE) < @MONTHNUM
SELECT @EBDATEMATCH = '输入日期小于计费月份'
IF DATEPART(MONTH, @ENTRYDATE) > @MONTHNUM
SELECT @EBDATEMATCH = '输入日期大于结算月份'
结束
GO`
感谢 JNK 提供 DATEPART 提示。我肯定不是一个尿脑的人
CREATE PROCEDURE [sp_BillDateCheck]
@CURMONTH VARCHAR(3),
@MONTHNUM INT OUTPUT,
@EBDATEMATCH VARCHAR(90) OUTPUT
AS
BEGIN
DECLARE @ENTRYDATE DATETIME
SELECT @ENTRYDATE = GETDATE() --GET CURRENT ENTRY DATE
--GET MONTH NUMBER BY GIVING MONTH NAME
SELECT @MONTHNUM = DATEPART(mm,CAST(@CURMONTH + ' 1900' AS DATETIME))
IF DATEPART(MONTH, @ENTRYDATE) = @MONTHNUM
SELECT @EBDATEMATCH = 'ENTRY DATE AND BILLING MONTH ARE SAME'
IF DATEPART(MONTH, @ENTRYDATE) < @MONTHNUM
SELECT @EBDATEMATCH = 'ENTRY DATE IS LESS THAN BILLING MONTH'
IF DATEPART(MONTH, @ENTRYDATE) > @MONTHNUM
SELECT @EBDATEMATCH = 'ENTRY DATE IS GREATER THAN BILLING MONTH'
END
GO`
thanks to JNK for providing DATEPART hint. I am not a pee brain for sure