Move currentdate+1 to field when null?

Question

Move currentdate+1 to field when null?

jbalbo

SSCertifiable

Points: 7583
More actions
June 20, 2013 at 12:25 pm

#294952

Hi
I have a where clause
table.AdminDate <= @startdate AND table.DischargeDate >= @enddate
But I want to move currentdate+1 to Dischargedate if Dischargedate isnull ?
Thanks
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

When you say "move" - do you mean update the dischargedate in the table to currentdate + 1 or just treat it that way ion the where clause of this query.

DDL plus an example would help us understand.

Mike John

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Hi Mike

just treat it that way

I came up with this but not working

Where

AdminDate <= @startdate AND (CASE WHEN DischargeDate IS NULL THEN GETDATE()+1 else DischargeDate) >= @enddate

So if the discharge date is null just use today+1 as the comparison

Thanks

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Try this:

AND isnull(table.DischargeDate, dateadd(day, 1, getdate()) >= @enddate

Keep in mind that if the value of @enddate is greater than the current date plus 1 this will still evaluate to false.

With that in mind I think what you really want is something like this:

AND isnull(table.DischargeDate, dateadd(day, 1, @enddate) >= @enddate

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Will this work?

WHERE table.AdminDate <= @startdate AND ( table.DischargeDate >= @enddate OR table.DischargeDate IS NULL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/