how to use for each loop in sql server?

Question

I have a table tblEmpDetail which contains a column CreatedDate.

I want to add 72 hours to each created date, so if the created date is 2012-07-14 07:21:19.180 then I want the output to be 2012-07-17 07:21:19.180.

Can someone let me know how can I accomplish to this?

Actually what I want to do is to go to each row then check if the

getdate() - createddate

is equal to or less than 72 hours; then I need to add 72 hours to createddate. Otherwise I want that column to be null. I am pasting my code that what I have done.

Declare @PassedTime datetime
set @PassedTime= DATEDIFF(HH,Createddate, GETDATE())

if CONVERT(varchar(20),@PassedTime,108)>=CONVERT(varchar(20),'72:00:00',108)
begin
    select empno,empName,CreatedDate,dateadd(HH,72,CreatedDate)BD from tblEmpDetail 
end
else
begin
   select empno,empName,CreatedDate,'' as BD from tblEmpDetail
end

Just a tip from someone who has been at it a while. If you are writing a loop in SQL, you are doing it wrong. Try to rephraese the solution as a query. — JohnFx
– JohnFx, Commented Jul 15, 2012 at 2:47
Extra credit for calling GetDate() once and saving the value instead of chasing a moving target in the query. — HABO
– HABO, Commented Jul 15, 2012 at 11:40

John Gietzen · Accepted Answer · 2012-07-15 03:05:56Z

No looping is required.

SQL Server excels at doing "set based" queries.

To get a projection:

select
    CreatedDate,
    DateAdd(hour, 72, CreatedDate) [NewDate]
from
    tblEmpDetail

To update the table permanently:

update
    tblEmpDetail
set
    CreatedDate = DateAdd(hour, 72, CreatedDate)

If you MUST have a loop, you can use a cursor:

declare cur cursor fast_forward read_only for
select
    CreatedDate,
    DateAdd(hour, 72, CreatedDate) [NewDate]
from
    tblEmpDetail

-- here, you would use the cursor.

More information on cursors here: http://msdn.microsoft.com/en-us/library/ms180169.aspx

EDIT

Your query above can be done in a set based way like this:

select
    CreatedDate,
    CASE
        WHEN DateAdd(hour, 72, CreatedDate) <= GetDate() THEN NULL
        ELSE DateAdd(hour, 72, CreatedDate)
    END [NewDate]
from
    tblEmpDetail

@raman: I have added an example of what you are trying to do. I might have the logic backwards, but the idea is to use CASE WHEN as though it was an IF statement.

Collectives™ on Stack Overflow

how to use for each loop in sql server?

1 Answer 1

EDIT

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

1 Comment

Related