Comparing dates in Case statement

hi

I am having problem in converting dates either to varchar or integer

I need to compare (just month and date part , not the year)

If

mm/dd (of current date) i.e 07/12 > 06/30

i.e if today's month and date is greater than june 30th then perform task A

if today's month and date is less than july 1st perform task B

i.e

07/12 < 07/01

please help

Thanks

Code Snippet

select case when (month(getdate())* 100 + day(getdate())) > 0630

then ... -- task a

else ... --task b

end

from ....

|||

Thank you very much , that helped

But I have a new problem

I am trying to execute task A

as below

SELECT
case
when (month(getdate())* 100 + day(getdate())) > 0630
then
(select * from dbo.V_FUN_SCOPES_LEVELS0506)
end

It gives error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

|||

You need to use IF syntax instead of CASE syntax; give a look to the related article in books online. That should look something like:

Code Snippet

if (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506

Also, beware of using "SELECT *" syntax from within a stored procedure or function.

|||

yea, it won't work in that manner.

Can you elaborate a little more on what you're trying to do and how it is going to be used.

From this is looks like you'll need a stored procedure or maybe a function.

|||

I think in you're probably just looking at some standard IF...ELSE control of flow.

IF (SELECT (month(getdate())* 100 + day(getdate()))) > 0630

PRINT 'do this'

ELSE

PRINT 'do that'

|||

Hi

the IF syntax works independently but If i try to create a store proc as below it gives error

CREATE PROCEDURE [dbo].[P_Latest]

AS

IF (month(getdate())* 100 + day(getdate())) > 0630
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506

END

ELSE
IF (month(getdate())* 100 + day(getdate())) < 0701
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END

G0

I need to display select * from PROCEDURE [dbo].[P_Latest]

i.e Exec PROCEDURE [dbo].[P_Latest] from SQL reporting services

P.S. Here the view dbo.V_FUN_SCOPES_LEVELS0506 is as below

I need the subquery for Display purpose

(select
a.student_id,
a.at_sss_read_score as Score_06,
b.at_sss_read_score as Score_07,
a.at_test_month + '/' + '20' + a.fcat_test_year as Date_06,
b.at_test_month + '/' + '20' + b.fcat_test_year as Date_07,
a.at_test_month as Month_06,
b.at_test_month as Month_07,
a.at_test_year as Year_06,
b.at_test_year as Year_07,
a.at_sss_read_level as Level_06,
b.at_sss_read_level as Level_07

from
(
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate())-1 , 2)) a
--AND STUDENT_ID IN ('0011307')) a

full join

(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate()),2 )) b
--AND STUDENT_ID IN ('0011307')) b

on a.student_id = b.student_id
)

where
a.at_test_year is not null
and b.at_test_year is not null

)

|||

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

|||

In your Reporting Services dataset, just set the command type to StoredProcedure and the QueryString to [dbo].[P_Latest].

That is the equivalent of select * from ...

|||

I chose to believe that that was just test code

|||

Kent Waldrop Jl07 wrote:

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

I've always heard this... "...SELECT * from inside a procedure is a bad idea.." without really understanding why. I apologize if i'm deviating from the point but...

when you say compile time and not run time, do you mean if a store procedure is compiled with select * then subsequently, the table changes (ie. column added) the sp will return everything without the extra column?

|||Yes, that is exactly what I mean; it is for that reason that I will sometimes say that use of "SELECT *" leaves "land mines" that blow up sometime later.