updating the date filed of a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

updating the date filed of a column

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-03 : 14:05:27

Kindly help me in updating the date fileld of the table A

tableA has 2 columns as values as below

A(datefrom, dateto)


datefrom       dateto 

------------------------------------------

NULL           6/8/2008

NULL           14/8/2008

NULL           18/8/2008

I want to update the datefrom field of table A as below

final out put as below


datefrom       dateto 

------------------------------------------

NULL                 6/8/2008

7/8/2008            14/8/2008

13/8/2008          18/8/2008

either a sql query or a procedure any thing

Kindly help me in doing so as I am new to T-Sql

Thanks,

aak

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-03 : 16:17:47

Update a
Set DateFrom = dateadd(month,1,(Select max(aa.DateTo) from mytable aa where aa.DateTo < a.DateTo))
from
Mytable a

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-04 : 00:08:49

quote:
Originally posted by Vinnie881

Update a
Set DateFrom = dateadd(month,1,(Select max(aa.DateTo) from mytable aa where aa.DateTo < a.DateTo))
from
Mytable a

Sorry,I misspelled the second datefrom date, instead of 15/08/2008 writing 13/08/2008.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:15:42

[code]UPDATE a
SET a.datefrom =DATEADD(d,-1,b.dateto)
FROM A a
CROSS APPLY(SELECT TOP 1 dateto
FROM A
WHERE dateto < a.dateto
ORDER BY dateto DESC)b[/code]

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-04 : 03:49:24

quote:

Originally posted by visakh16

UPDATE a
SET a.datefrom =DATEADD(d,-1,b.dateto)
FROM A a
CROSS APPLY(SELECT TOP 1 dateto
            FROM A
            WHERE dateto < a.dateto
            ORDER BY dateto DESC)b

Thanks

The first column of datefrom can be null from second onwards it should show the 7/8/2008 and second value 15/8/2008.. and so on..

Thanks again..

The first

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 04:00:37

sorry it should be

UPDATE a
SET a.datefrom =DATEADD(d,1,b.dateto)
FROM A a
CROSS APPLY(SELECT TOP 1 dateto
            FROM A
            WHERE dateto < a.dateto
            ORDER BY dateto DESC)b

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-04 : 05:09:00

quote:

Originally posted by visakh16

sorry it should be

UPDATE a
SET a.datefrom =DATEADD(d,1,b.dateto)
FROM A a
CROSS APPLY(SELECT TOP 1 dateto
            FROM A
            WHERE dateto < a.dateto
            ORDER BY dateto DESC)b

the above query is updating the same value as in dateto to datefrom
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 05:21:14

quote:
Originally posted by aakcse

quote:
Originally posted by visakh16

sorry it should be

UPDATE a
SET a.datefrom =DATEADD(d,1,b.dateto)
FROM A a
CROSS APPLY(SELECT TOP 1 dateto
FROM A
WHERE dateto < a.dateto
ORDER BY dateto DESC)b

the above query is updating the same value as in dateto to datefrom
Thanks.

show your actual query please.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-04 : 06:05:19

Thanks Sir,
below is my query which I executed.

update p
set p.[pay_period_from]=

dateadd(d,1,b.[pay_period_to])
from payslips p
cross apply(select top 1 [pay_period_to]
from payslips
where [pay_period_to] < p.[pay_period_to]
order by [pay_period_to] desc)b

aakcse
Aged Yak Warrior

570 Posts

Posted - 2008-09-04 : 06:19:18

thanks now its working fine, if there is any thing i will get back to you

thank you so much Sir.

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 06:24:27

quote:
Originally posted by aakcse

thanks now its working fine, if there is any thing i will get back to you

thank you so much Sir.

Regards,
aak

No worries. you're always welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources