Issue with the IIF statement while migrating

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

Issue with the IIF statement while migrating

Author

Topic

RamanathBhat
Starting Member

5 Posts

Posted - 2009-06-02 : 03:22:00

Suppose this is the existing query for MS Access..

Select tab1.col1, tab2.col2 from table1 tab1,table2 tab2 where
Tab1.col3 = tab2.col3 and iif((day(convert(varchar,getdate(),101)) between 1 and 23),
convert(varchar,getdate(),101) between tab1.col4 and tab1.col5,
convert(varchar,getdate(),101)between tab2.col4 and tab2.col5)
order by tab1.col1,tab2.col2

The equivalent case when statement I used is something like this

Select tab1.col1, tab2.col2 from table1 tab1,table2 tab2 where
Tab1.col3 = tab2.col3 and case when day(convert(varchar,getdate(),101)) between 1 and 23
then convert(varchar,getdate(),101) between tab1.col4 and tab1.col5 --------? I get an error here saying syntax error near between operator.
else
convert(varchar,getdate(),101)between tab2.col4 and tab2.col5
end
order by tab1.col1,tab2.col2

Please ignore minor logical errors, as the above example I sent is just meant to give a rough idea regarding my doubt. I want the emboldened part to be executed as a part of the main query based on the logical expression(Italicized) in the iif statement.

looking forward for some suggestions

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 03:59:29

case
when datepart(day,getdate()) between 1 and 23 then ...

But what should this do?
"convert(varchar,getdate(),101) between tab1.col4 and tab1.col5"

No, you're never too old to Yak'n'Roll if you're too young to die.

RamanathBhat
Starting Member

5 Posts

Posted - 2009-06-02 : 04:23:55

quote:
Originally posted by webfred

case
when datepart(day,getdate()) between 1 and 23 then ...

But what should this do?
"convert(varchar,getdate(),101) between tab1.col4 and tab1.col5"

No, you're never too old to Yak'n'Roll if you're too young to die.

the convert basically gives the current date

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 04:27:10

Yes, but what's about "between tab1.col4 and tab1.col5" and "between tab2.col4 and tab2.col5"?
I have no clue, what this should do.

No, you're never too old to Yak'n'Roll if you're too young to die.

RamanathBhat
Starting Member

5 Posts

Posted - 2009-06-02 : 04:35:19

quote:
Originally posted by webfred

Yes, but what's about "between tab1.col4 and tab1.col5" and "between tab2.col4 and tab2.col5"?
I have no clue, what this should do.

No, you're never too old to Yak'n'Roll if you're too young to die.

Oops!! i am sorry i didnt mention it, consider col4 and col5 to be two date fields and i want the check if the current date lies between col4 and col5 (i basically want to pick date values from different tables)..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 05:07:36

[code]
WHERE Tab1.col3 = tab2.col3
and (
((datepart(day,getdate()) between 1 and 23) and (convert(varchar,getdate(),101) between tab1.col4 and tab1.col5))
OR
((datepart(day,getdate()) > 23) and convert(varchar,getdate(),101)between tab2.col4 and tab2.col5))
)
[/code]
I am not sure about converting a datetime to varchar and then check if this is between cols. What datatypes are col4 and col5 in the tables 1 and 2?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 05:11:06

By the way: I have tried to avoid CASE in WHERE and hope I have done it right so far.
See Jeffs: http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

RamanathBhat
Starting Member

5 Posts

Posted - 2009-06-02 : 05:16:55

the columns are of time datetime.. i even tried casting like this
cast(convert(varchar,getdate(),101) as datetime)

but it didnt work

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 05:28:13

So you don't have to convert anything because getdate gives already datetime...

No, you're never too old to Yak'n'Roll if you're too young to die.

RamanathBhat
Starting Member

5 Posts

Posted - 2009-06-02 : 05:45:33

Hey webfred, i tried to avoiding case/if statements in the where clause, restructured the query, its a bit long and redundant now but works brilliantly fine :)

Do let me know if there is a smaller way to do it

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-02 : 14:33:38

I realize this doens;t add much to the conversation, but I suggest that you avoid implict conversion in your code. I see people getting burned by this all the time. Instead of:

WHERE Tab1.col3 = tab2.col3 
and (
      ((datepart(day,getdate()) between 1 and 23) and (convert(varchar,getdate(),101) between tab1.col4 and tab1.col5))
       OR
      ((datepart(day,getdate()) > 23) and convert(varchar,getdate(),101)between tab2.col4 and tab2.col5))
    )

Try something like:

WHERE Tab1.col3 = tab2.col3 
and (
      ((datepart(day,current_timestamp) between 1 and 23) and (dateadd(day, datediff(day, 0 , current_timestamp), 0) between tab1.col4 and tab1.col5))
       OR
      ((datepart(day,current_timestamp) > 23) and dateadd(day, datediff(day, 0 , current_timestamp), 0) between tab2.col4 and tab2.col5))
    )

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 15:08:10

Dear Lamprey,
I would say that
convert(varchar,getdate(),101)
is NOT a kind of implicit conversion - it is explicit.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-02 : 15:30:09

quote:
Originally posted by webfred

Dear Lamprey,
I would say that
convert(varchar,getdate(),101)
is NOT a kind of implicit conversion - it is explicit.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Correct, you are Explitily converting it to a VARCAR. Then sql is implicitly converting it back to a DATETIME.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources