Tags: case, compare, comparing, converting, database, date, dates, integer, microsoft, mysql, oracle, server, sql, statement, varchar

Comparing dates in Case statement

On Database » Microsoft SQL Server

8,485 words with 11 Comments; publish: Sun, 06 Jan 2008 11:21:00 GMT; (25078.13, « »)

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

All Comments

Leave a comment...

  • 11 Comments
    • Code Snippet

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

      then ... -- task a

      else ... --task b

      end

      from ....

      #1; Tue, 02 Oct 2007 01:54:00 GMT
    • 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.

      #2; Tue, 02 Oct 2007 01:55:00 GMT
    • 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.

      #3; Tue, 02 Oct 2007 01:56:00 GMT
    • 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.

      #4; Tue, 02 Oct 2007 01:57:00 GMT
    • 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'

      #5; Tue, 02 Oct 2007 01:58:00 GMT
    • 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

      )

      #6; Tue, 02 Oct 2007 02:00:00 GMT
    • 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?

      #7; Tue, 02 Oct 2007 02:01:00 GMT
    • 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 ...

      #8; Tue, 02 Oct 2007 02:02:00 GMT
    • I chose to believe that that was just test code

      #9; Tue, 02 Oct 2007 02:03:00 GMT
    • 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?

      #10; Tue, 02 Oct 2007 02:04:00 GMT
    • 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.
      #11; Tue, 02 Oct 2007 02:05:00 GMT