Update field or the other if null

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

General SQL Server Forums

New to SQL Server Programming

Update field or the other if null

Author

Topic

tech1
Starting Member

49 Posts

Posted - 2010-06-22 : 06:34:23

I want to update a record.
there is a condition.

condition is, to see if today's date matches either the startdate field or the enddate field.

startdate is compulsary so has values and the enddate field is nullable.

so, if enddate is null, then update the record if startdate == getdate() else, if enddate is not null then update the record if enddate == getdate()

how can I write this query in SQL?

this is what I currently use for the startdate, and works fine:

quote:

UPDATE ActivityResults
SET StatusID = 4 /* Pending Feedback */

WHERE StatusID = 3
AND DATEADD(dd, 0, DATEDIFF(DD, 0, StartDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE()))

but I want to use "Enddate" instead of startdate IF there is a value in enddate, otherwise use startdate

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-22 : 06:47:41

Try this:

UPDATE ActivityResults
SET StatusID = 4 /* Pending Feedback */

WHERE StatusID = 3
AND (DATEADD(dd, 0, DATEDIFF(DD, 0, StartDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE()))
or
(
DATEADD(dd, 0, DATEDIFF(DD, 0, EndDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE()))
)
)

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-22 : 07:31:30

Hello, you can try it:

UPDATE
ActivityResults
SET
StatusID=4
WHERE
StatusID=3 AND
((EndDate IS NULL AND StartDate=convert(datetime,convert(varchar(10),getdate(),104),104)) OR
(EndDate IS NOT NULL AND EndDate=convert(datetime,convert(varchar(10),getdate(),104),104)))

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-22 : 08:39:48

quote:
Originally posted by Devart

Hello, you can try it:

UPDATE
ActivityResults
SET
StatusID=4
WHERE
StatusID=3 AND
((EndDate IS NULL AND StartDate=convert(datetime,convert(varchar(10),getdate(),104),104)) OR
(EndDate IS NOT NULL AND EndDate=convert(datetime,convert(varchar(10),getdate(),104),104)))

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

Dont convert dates to varchars. Refer this to know how effectively you can use date values in the WHERE clause
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/21/understanding-datetime-column-part-iii.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-22 : 10:52:03

quote:
Originally posted by tech1

I want to update a record.
there is a condition.

condition is, to see if today's date matches either the startdate field or the enddate field.

startdate is compulsary so has values and the enddate field is nullable.

so, if enddate is null, then update the record if startdate == getdate() else, if enddate is not null then update the record if enddate == getdate()

how can I write this query in SQL?

this is what I currently use for the startdate, and works fine:

quote:

UPDATE ActivityResults
SET StatusID = 4 /* Pending Feedback */

WHERE StatusID = 3
AND DATEADD(dd, 0, DATEDIFF(DD, 0, StartDate)) =
DATEADD(dd, 0, DATEDIFF(DD, 0, GETDATE()))

but I want to use "Enddate" instead of startdate IF there is a value in enddate, otherwise use startdate


WHERE COALESCE(EndDate,StartDate) >= DATEADD(dd,  DATEDIFF(DD, 0, GETDATE()),0)
AND COALESCE(EndDate,StartDate) < DATEADD(dd, DATEDIFF(DD, 0, GETDATE()),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources