How Do I Add Time with integer value in MSSQL ?

Hi, Expert

How Do I Add Time with integer value in MSSQL ??

For Example: 07:00:50 + 20 = 07:01:10

Note: 20 is in second

Thanks in advance

Check out the DATEADD function in BOL (documentation).|||

Hi,

You can play with DATEADD function for your question, for example:

SELECT dateadd(s, 20, getdate())

this one will add 20 seconds to now.

You can look up this function from Books Online to find more information. By the way, in SQL Server there is datatime data type, but not time or date only data type.

How Do I Add 1 to a Field?

I have a Table: Thread

There are 2 relevant fields: ThreadID and Counter (integer).

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

I know how to do this as 2 stored procedures (one to get the current value of the counter and a second procedure to write the new value).

There must be a better way to do it as one procedure though.

Any suggestions?

Thanks,

Chris

You can do this in a number of ways. get the number and add the 1 at the application layer. or even as simple as

SELECT counter+1

FROM Thread

WHERE Threadid = @.Threadid

|||

The example you give will return a number 1 larger, but I also need to have that new number written into the data.

Can I do that with 1 stored procedure?

Chris

|||

Chris Messineo:

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

Thats what you wanted right?

|||

I'm sorry if my question is confusing.

I need to get the new value, but I also need to set that value in the db. I believe your solution will just get me the new value.

Does that make sense?

Chris

|||

do an UPDATE then. You can write a proc that will do the update and return the value. you can either return using an OUTPUT parameter or a SELECT statement.

How Change field value triggered by date?

In a table i had field called Status and it is of type integer and another two field From and To ... they are of type date .....
what i want to do is to change the value of status field when the current date become equal to the From field and also make another change when the current date become equal to the To field ...

So How can i do This on SQL server 2005?

You need to run a query to update the rows, something like this

UPDATE mytable
SET Status = CASE
WHEN datediff(d, From, getdate()) = 0 THEN 1
WHEN datediff(d, To, getdate()) = 0 THEN 2
END

You could create a calculated column based on the same expression if you want status to always be up to date without running the query first. Otherwise schedule a job to run that query every night so that the status column is always correct.

|||

Thank You for your reply and it is helpful,

But what if the From and To fields in a child table to the original table that have the field status?

e.x. Master Table have the following fields:

PersonID, Name, Status

Details Table has the following fields:

ID,PersonID (as foreign key to the master table), From, To

How the query will look like?!

|||

UPDATE mastertable
SET Status = CASE
WHEN datediff(d, detailtable.From, getdate()) = 0 THEN 1
WHEN datediff(d, detailtable.To, getdate()) = 0 THEN 2
END
FROM mastertable
INNER JOIN detailtable ON mastertable.PersonID = detailtable.PersonID