Conditional Update - SQL Server Forums

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)

Conditional Update

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-11-25 : 06:29:39

I have a script:

UPDATE PaymentArrangements

SET ArrangementDate = FirstPaymentDate,
DayOfMonth = DATEPART(DAY,FirstPaymentDate),
NextPaymentDate = DATEADD(MONTH,1,FirstPaymentDate),
ExpiryDate = DATEADD(MONTH,6,GETDATE())

WHERE CreatedBy = 93

but I've just released that I cannot have a DayOfMonth > 28. How can I correc tthe above script to prevent this please?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-11-25 : 06:43:11

Of course I realise that could just do:

UPDATE PaymentArrangements

SET ArrangementDate = FirstPaymentDate,
DayOfMonth = DATEPART(DAY,FirstPaymentDate),
NextPaymentDate = DATEADD(MONTH,1,FirstPaymentDate),
ExpiryDate = DATEADD(MONTH,6,GETDATE())

WHERE CreatedBy = 93 AND DATEPART(DAY,FirstPaymentDate) < 29

UPDATE PaymentArrangements

SET ArrangementDate = FirstPaymentDate,
DayOfMonth = 28,
NextPaymentDate = DATEADD(MONTH,1,FirstPaymentDate),
ExpiryDate = DATEADD(MONTH,6,GETDATE())

WHERE CreatedBy = 93 AND DATEPART(DAY,FirstPaymentDate) > 28

I just wondered if there was a more elegent way.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 07:41:53

UPDATE PaymentArrangements

SET ArrangementDate = FirstPaymentDate,
DayOfMonth = case when DATEPART(DAY,FirstPaymentDate) < 29 then DATEPART(DAY,FirstPaymentDate) else 28 end ,
NextPaymentDate = DATEADD(MONTH,1,FirstPaymentDate),
ExpiryDate = DATEADD(MONTH,6,GETDATE())

WHERE CreatedBy = 93

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources