CASE Statement - 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

General SQL Server Forums

New to SQL Server Programming

CASE Statement

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-04-27 : 01:09:47

Hi,
how to use CASE Statement in where clause which compares two date columns.

Thanks in advance.

$atya.

Love All Serve All.

onlyforme
Starting Member

25 Posts

Posted - 2009-04-27 : 02:16:49

Hi,
Check the following.I think this will help u.

select * from table1 where startdate in(select case when id=1 then startdate else enddate end from table1) and startdate>=enddate

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-27 : 02:18:32

WHERE CASE WHEN DateField1 > DateField2 THEN 1 ELSE O END =1

Above query would returns only records those have DateField1 > DateField2 .

Dev@nlkss

134 Posts

Posted - 2009-04-27 : 04:36:30

plz correct the following query and tell me where am doing wrong.

select * from Log
where case when d1 is null then d1 between getdate() and getdate()+7 else d2 between getdate() and getdate()+7 end

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 04:43:11

What?
If d1 is null, how can you do a BETWEEN with d1?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 04:43:45

Are you sure you didn't mean

WHERE COALESCE(d2, d1) BETWEEN getdate() and getdate() + 7

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2009-04-27 : 05:08:37

yes,its works Absolutely.
cant i use CASE in where as follows,

select * from Log
where case when d1 is null then d2 between getdate() and getdate()+7 else d1 between getdate() and getdate()+7 end

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 05:15:08

You probably can.
Try once and see if there is a difference.

E 12°55'05.63"
N 56°04'39.26"

Kokkula
Starting Member

41 Posts

Posted - 2009-04-27 : 09:19:34

Hello,

You can write the query in different ways. Here are the samples


GO

SELECT *
FROM Log
WHERE (CASE WHEN D1 IS NULL THEN D2 ELSE D1 END) BETWEEN GETDATE() AND GETDATE() + 7
GO

SELECT *
FROM Log
WHERE GETDATE() <= (CASE WHEN D1 IS NULL THEN D2 ELSE D1 END)
AND GETDATE() + 7 >= (CASE WHEN D1 IS NULL THEN D2 ELSE D1 END)

Hope helpful...

Thanks,
Pavan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources