Modify a date in SQL Server

Question

Modify a date in SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

My problem is that I want to add a certain number of hours and minutes to a date and save it. The problem is that it marks me an error in the type of data conversion. How can I modify this date so I can save it with the hours and minutes already added?

SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_horarios] ON [dbo].[Citas]
AFTER INSERT
AS
BEGIN
DECLARE @hora smallint = (SELECT DATEPART(HOUR,Fecha) FROM inserted)
DECLARE @minutos smallint = (SELECT DATEPART(MINUTE,Fecha) FROM inserted)
DECLARE @duracion float = (SELECT Duracion FROM inserted)
DECLARE @fecha datetime = (SELECT Fecha FROM inserted)
DECLARE @fechafin datetime = @fecha
SET @fechafin = DATEADD(hour,@hora,@fechafin)
SET @fechafin = DATEADD(minute,@minutos,@fechafin)
INSERT INTO dbo.Horarios
values(@fecha,@fecha,@fechafin)
END

sql sql-server

asked by Manuel Nava 30.04.2018 в 11:42

source

2 answers

Convert line from vb to C # Oracle Sql XE - Error entering sqlplus [Linux deepin (based on debian)]

score 1 · Answer 1

In SQL statements you can not add dates, so you'll have to pass it to seconds and add up the difference, for that you have the statement DATEDIFF and SUM

therefore it would be something like this:

SET @Segundos = SELECT SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME]))

to add up the difference in seconds

and to convert it to a datetime type you should apply the following:

SELECT CONVERT(varchar(8), DATEADD(second, @Segundos, 0))

score 0 · Answer 2

First of all, your insert is:

INSERT INTO dbo.Horarios
values(@fecha,@fecha,@fechafin)

It is not good not to indicate the names of the columns in insert , as it is now, I imagine that the Horarios table has only 3 columns, but this insert would not work and there the main problem of not indicate the columns, if we add fields, the insert written like this, stop working. The optimal syntax would be:

INSERT INTO dbo.Horarios (fecha1, fecha2, fecha3)
values(@fecha,@fecha,@fechafin)

The names are fictitious because I do not really know what they are called in the table.

The other issue is, is the way to calculate a new date based on a start date and duration. You have not clarified in which unit the percentage% of the date is measured, but imagine how likely it is that it be in minutes. If this were the case, you do not need to declare variables to do the calculations and it may not be advisable either. We could directly do the Duracion of the table insert that has the row or rows just that they have been inserted. Something like that

ALTER TRIGGER [dbo].[tr_horarios] ON [dbo].[Citas] AFTER INSERT
AS
BEGIN

    INSERT INTO dbo.Horarios
        SELECT fecha,
               fecha,
               DATEADD(minute, duracion, fecha)
               FROM inserted

END

Finally, you have to be clear, that unlike other database engines, in SQL Server the trigger is executed by sentence, ie if you insert a record or 1,000,000 does not matter, the trigger will execute a once. What you tried to do using variables might eventually work when you insert a single row, but it would not work the same if you inserted it wrong. The form that indicates, that of directly inserting the results of inserted is consistent with the operation of the triggers.