Hold server datetime for some days

Question

0.00/5 (No votes)

See more:

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 .

Posted 21-Mar-17 0:21am

Rujj

Updated 21-Mar-17 5:07am

v2

Add a Solution

Comments

CHill60 21-Mar-17 6:26am

Are you implying that you don't want to change the existing column defaults nor do you want to change the application to stop providing the datetime?

Rujj 21-Mar-17 6:37am

Yes, because after some days i will be reverting back to default settings.

ZurdoDev 21-Mar-17 6:51am

Just write some code to do it. Where are you stuck?

_Asif_ 21-Mar-17 6:59am

I am more interested in knowing what triggered the desire behavior

3 solutions

Solution 3

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.

Posted 21-Mar-17 5:07am

Slacker007

Solution 1

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!

Posted 21-Mar-17 1:04am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-03-21T02:26:00

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.

SQL

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

I used a very simple table:

SQL

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

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.

SQL

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)

SQL

-- 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

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

SQL

DELETE FROM FixedDate