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]
ASIF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506G0
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.
