I try to create a table that serves as a control for several tables whose structure is based on a day (24 hours) divided into 30-minute intervals.
In another table I have a list of activities, these last 30 minutes each. The point is that 2 activities cannot collide in the calendar.
To do this, create this control table.
SQL-Server
CREATE TABLE dbo.Control_Actividades
(
Id_Tiempo datetime NOT NULL PRIMARY KEY,
Id_Actividad varchar(50) NULL
) ON [PRIMARY]
The point is that I want the field to Id_Tiempo
be auto-incremental at 30 minute intervals. I've searched on various sites but I can't find any articles talking about this.
More details.
I apologize for not giving many details of what I'm doing, but it could be summarized in a log of future activities. I just wanted to clarify if it was possible to make a DateTime autoincrement. I know it's not possible.
I understand that there is no autoincrement at the date level, nor is it very clear to me what you need, because ultimately you could have one
INT IDENTIY(0,1800)
that basically increases in ranges of 1800, which measured in seconds is exactly 30 minutes and then simply use theDATEADD
addend the id in seconds to any date.But yes, yes or yes you want a date field with that displacement, with the method that I mentioned you can do something closer to what you want:
This defines a field
DateId
that with each insert will generate a date 30 minutes higher from the01-01-2017
. But this has a problem, the field is "calculated", in reality it does not physically exist, so we cannot use it in an index or primary key, for that we need it to be "persistent", something that is also complicated because itDATEADD(s, ID, '20170101')
is non-deterministic, that is, the engine cannot assure us that the value returned by the function will always be the same, to make it deterministic and persistent we have to create an additional date field in the table, something like this:Regardless of how we are going to create the table, we can do the following:
The output would be something like this:
I repeat to you, that it is hard for me to see the need to have the Date field like this, and that you investigate on the side of a
IDENTITY
classic since what I told you is not very optimal.