4-4-5 会计期间是我生存的祸根
我们使用 4-4-5 会计期间。如果您对此不熟悉，可以在 维基百科 中找到信息。
为了实现这一目标，我创建了一个日期表，其中包含未来 10 年的所有日期。我使用了此处找到的脚本（尽管已修改）。
我的表的创建脚本是：
USE [RedFridayDates];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [tblRedFridayAllDates] (
[DATE_ID] int NOT NULL,
[DATE] datetime NOT NULL,
[YEAR] smallint NOT NULL,
[MONTH] tinyint NOT NULL,
[DAY_OF_WEEK] tinyint NOT NULL,
[REDFRIDAY] bit NULL,
[Period] tinyint NULL,
[ForecastSales] money NULL)
ON [PRIMARY];
GO
期间是我的 4-4-5“月”。
我通常使用我创建的以下模板将查询链接到它：
SELECT RED.[YEAR] as [Year],
RED.PERIOD as [RF Period],
FROM TableName
INNER JOIN REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED
ON RED.date =
CAST (FLOOR (CAST (TableName.Date AS FLOAT)) AS DATETIME)
我的大部分数据库使用的是 SQL 2000，因此我的日期都是日期时间字段。
有没有更有效的方法来实现这一目标？这是我可以使用的最佳查询模板吗？将日期转换为日期时间的其他方法怎么样？有更快的方法吗？
We use 4-4-5 Accounting Periods. In case you aren't familiar with this, you can find info at Wikipedia.
To accomplish this, I created a date table with all the days for the next 10 years. I used the script (albeit modified) found here.
The creation script for my table is:
USE [RedFridayDates];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [tblRedFridayAllDates] (
[DATE_ID] int NOT NULL,
[DATE] datetime NOT NULL,
[YEAR] smallint NOT NULL,
[MONTH] tinyint NOT NULL,
[DAY_OF_WEEK] tinyint NOT NULL,
[REDFRIDAY] bit NULL,
[Period] tinyint NULL,
[ForecastSales] money NULL)
ON [PRIMARY];
GO
The Period is my 4-4-5 "month".
I typically link my queries to it with the following template I created:
SELECT RED.[YEAR] as [Year],
RED.PERIOD as [RF Period],
FROM TableName
INNER JOIN REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED
ON RED.date =
CAST (FLOOR (CAST (TableName.Date AS FLOAT)) AS DATETIME)
Most of my database usage is SQL 2000 so my dates are all datetime fields.
Is there a more efficient way to accomplish this? Is this the best Query template I could use? What about other ways to convert Date to Date time? Are there faster ways to do so?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我已经使用将日期时间的日期部分提取到持久计算字段（可能已索引）中，并对计算字段（仅日期）进行所有连接和搜索。实际上，您将在 2k8 之前获得 2k8 新的
DATE类型，但需要 8 个字节（如果您愿意将其存储为int）。优点是仅通过日期部分来实现日期时间的 SARGability。I have used extracting the date part of the datetime into a persisted computed field (maybe indexed) and do all joins and searches on the computed field (date only). In effect you'd have the 2k8 new
DATEtype in pre-2k8, at the expense of 8 bytes (or even 4 bytes if you're willing to store it asint). The advantages is SARGability of datetimes by only date part.