Case Statement to choose a date for comparison in where clause

[BHScripter]

Hi:

In my where clause I need to choose records based on a particular date to compare against two date columns. If the date from the database is older than getdate() than I want to compare getdate() against the two date columns if the date is newer (or in the future) than getdate() I want to use the database date.

I tried the following knowing it wasn't quite right - but I could hope. Any help would be appreciated.

case when tbl1.dtready < getdate() then between tbl2.dtstartdate and tbl2.dtenddate else getdate() between tbl1.dtstartdate and tbl2.dtenddate end

 

[gmmastros]

try this:

Case When tbl1.dtready < getdate() 
     Then tbl1.dtready
     Else GetDate()
     End between tbl2.dtstartdate and tbl2.dtenddate

If this works and you would like for me to explain it, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BHScripter]

Now its only pulling records where the date of comparison is in the future.

 

[gmmastros]

Can you show some sample data and expected results? This will make it easier to give you more accurate advice.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BHScripter]

I flipped the sign and now it works. I'm going to try and figure that one out.

 

[BHScripter]

flipping the sign was appropriate. Thanks so much for the help. I now understand the correct syntax for this kind of case statement thanks to your help. Most appreciated!

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom