保留服务器日期时间几天 [英] Hold server datetime for some days

问题描述

我在服务器上运行带有SQL Server 2012的ASP.NET MVC应用程序，无论何时插入记录，当前日期时间都会插入到表中。



对于某些列，默认值已设置为getdate（），对于某些列，它是通过DateTime.Now（）从应用程序获取的。



所以请考虑今天的日期为 2017/03/21 11:00 am ，当插入数据时需要这段时间但是明天插入数据时（2017/03/22 11:00 am）它应该插入与2017/03/21上午11:00相同的日期时间。



几天之后，每当数据插入表格时我都应该能够获得当前的日期时间。这可以在数据库级别或服务器级别实现吗？



提前谢谢。



我尝试了什么：



我正在尝试更改存储过程和在表中设置的默认值。

但是我正在尝试为它提供一些更可行的解决方案，所以我不需要更改数据库或应用程序。



另外我可以停止服务器时间同步domain。

I am running an ASP.NET MVC application with SQL Server 2012 on a server and whenever a record is inserted, the current datetime is inserted into the table.

For some column, the default value has been set to getdate(), and for some columns it is getting from application by DateTime.Now().

So consider today's date as 2017/03/21 11:00 am, when data is inserted it will take this time but when data is inserted tomorrow (2017/03/22 11:00 am) it should insert the same datetime as 2017/03/21 11:00 am.

And after some days I should again be able to get current datetime whenever data is inserted in table. Can this be achieved at database level or server level?

Thank you in advance.

What I have tried:

I am trying to change at stored procedure and default values set at table.
But i am trying to get some more feasible solution for it, so I don't need to change in database or application.

Also I can stop server time sync from another domain .

推荐答案

在以前的工作中，我们在日期使用类似的机制只是为了强制特定的工作日发挥作用。我们在紧急情况和测试环境中使用它。



实现类似的东西的一种方法是将覆盖日期时间存储在某种站立数据表中。例如。

In a previous employment we used a similar mechanism on date only to force a specific business day into play. We used it in emergency situations and in the testing environment.

One way of achieving something similar to have the override datetime stored in some sort of standing data table. E.g.

create table FixedDate
(
	FixedDate DateTime
)
INSERT INTO FixedDate VALUES (cast('2016-01-01 11:15:10' as datetime))

我使用了一个非常简单的表：

I used a very simple table:

create table DemoDate
(
	id int identity(1,1),
	DateToRecord DateTime,
	SomeOtherData nvarchar(125)
)

Cr在目标表上触发一个查询Fixeddate的触发器，如果​​可用则使用它，否则使用当前日期和时间。

Create a trigger on the target table that queries the Fixeddate and uses it if it is available otherwise uses the current date and time.

IF OBJECT_ID('TRG_DemoDate') IS NOT NULL
DROP TRIGGER TRG_DemoDate
GO

CREATE TRIGGER TRG_DemoDate ON dbo.DemoDate
AFTER INSERT AS
BEGIN
	DECLARE @fdate DATETIME = (SELECT FixedDate FROM FixedDate)
	IF @fdate IS NULL SET @fdate = GETDATE()

	UPDATE D SET DateToRecord = @fdate
	FROM DemoDate D
	INNER JOIN INSERTED I ON D.id=I.id
END
GO

然后我运行了以下查询（预期结果在评论中给出）

I then ran the following queries (expected results are given in the comments)

-- Date will be provided from FixedDate
INSERT INTO DemoDate (SomeOtherData) VALUES('asdfasdlf')
-- Date provided will be overridden
INSERT INTO DemoDate (SomeOtherData, DateToRecord ) VALUES('11111asdfasdlf', GETDATE()) 
-- Date will be set to GETDATE()
DELETE FROM FixedDate
INSERT INTO DemoDate (SomeOtherData) VALUES('22222asdfasdlf')
-- Date provided will be overridden
INSERT INTO DemoDate (SomeOtherData, DateToRecord ) VALUES('33333asdfasdlf', CAST('2017-01-01 11:11:11' as datetime) )
SELECT * FROM DemoDate

结果如下：

These were the results:

1	2016-01-01 11:15:10.000	asdfasdlf
2	2016-01-01 11:15:10.000	11111asdfasdlf
3	2017-03-21 12:21:11.033	22222asdfasdlf
4	2017-03-21 12:21:11.043	33333asdfasdlf

请注意，在我的示例中提供了任何日期时间在插入过程中被触发器中的规则完全覆盖。如果你想在关闭的情况下保留提供的日期，那么你还需要检查已经在INSERTED表上的日期。



到关闭FixedDate只是从常设数据表中删除数据

Note that in my example any datetime provided during the insert is completely overridden by the rules in the trigger. If you want to retain the provided date when the fixeddate is "switched off" then you will need to also check for a date already on the INSERTED table.

To "switch off" the FixedDate just delete the data from the standing data table

DELETE FROM FixedDate

如果您的要求要求您的输入日期灵活，请为替换日期添加新列。您可能需要相应地调整代码库。您不应该混淆表中的Created和Modified日期列，因为这些是真相的来源，可以这么说。

Add a new column for an Override Date if your requirement requires your input dates to be flexible. You may need to adjust your code base accordingly. You should not mess with the Created and Modified date columns in your table as those are sources of truth, so to speak.

没有内置方法可以做到这一点，你需要在C＃或VB软件中执行此操作。问题是，只有你知道什么时候应该以这种方式覆盖日期，并且没有机制为你做这件事。



所以找出工作原理什么时候应该 - 而且不应该 - 完成的规则​​，并在你的C＃或VB代码中实现它们。我们不能为你做到这一点，我们不知道你的规则是什么 - 这是一个非常奇怪的要求！

There is no built in way to do that, you would need to do that in your C# or VB software. The problem is that only you know when the date should be "overridden" in that way, and there is no mechanism to do that for you.

So work out the rules for when it should - and should not - be done, and implement those in your C# or VB code. We can't do that for you, we have no idea what your rules might be - and that is a very odd requirement!

这篇关于保留服务器日期时间几天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！