Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Current month prior month function

    It works but its slopy could anyone give me a few hints
    DECLARE @InputMoParam datetime
    ,@CurrentMoParam datetime


    SELECT @InputMoParam ='1/1/2015'
    ,@CurrentMoParam ='2/1/2015'

    SELECT CASE WHEN
    (DatePart(mm ,@InputMoParam ) = DatePart(mm ,@CurrentMoParam )-1 AND
    DatePart(yy ,@InputMoParam ) = DatePart(yy ,@CurrentMoParam )) OR
    (DatePart(mm ,@InputMoParam ) =12 AND
    DatePart(mm ,@InputMoParam )<> DatePart(mm ,@CurrentMoParam )AND
    DatePart(yy ,@InputMoParam ) = DatePart(yy ,@CurrentMoParam ) -1)
    THEN 'PRIOR Mo'
    ELSE CASE WHEN (DatePart(mm ,@InputMoParam ) = DatePart(mm ,@CurrentMoParam ) AND
    DatePart(yy ,@InputMoParam ) = DatePart(yy ,@CurrentMoParam ))
    THEN 'CUrrent MONTH'
    ELSE 'Not Valid'
    END
    END

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Here is my interpretation. To run, wipe across everything but the first line and execute.

    Code:
    /* TEST SECTION simulating the input parameters to a stored procedure
    declare @InputMoParam date
    declare @CurrentMoParam date
    set @InputMoParam='2/1/2015'
    set @CurrentMoParam='2/1/2015'
    --*/
    
    --since @InputMonth will be used three times, calculate it once here
    declare @InputMonth tinyint
    set @InputMonth=DATEPART(month,@InputMoParam)
    
    --force all the dates to the first of the month
    set @InputMoParam=convert(varchar,@InputMonth)+'/1/'+datename(year,@InputMoParam)
    set @CurrentMoParam=convert(varchar,datepart(month,@CurrentMoParam))+'/1/'+datename(year,@CurrentMoParam)
    
    --do the testing
    if (datepart(month,@CurrentMoParam)=@InputMonth)
    	print 'Current Month'
    else
    	if (datepart(month,dateadd(day,-1,@CurrentMoParam))=@InputMonth)
    		print 'Prior Month'
    	else
    		print 'Not Valid'
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    If you use
    set @InputMoParam='2/1/2015'
    set @CurrentMoParam='2/1/2016'

    the function returns current month, i was thinking since both
    @InputMoParam
    @CurrentMoParam
    are set to 1st day of month,, why not just compare @InputMoParam = @CurrentMoParam

    but would have to make sure time portion (im on 2005) was set to 12am... How do ya do that?


    declare @InputMoParam datetime
    declare @CurrentMoParam datetime
    set @InputMoParam='2/1/2015'
    set @CurrentMoParam='2/1/2016'
    --*/

    --since @InputMonth will be used three times, calculate it once here
    declare @InputMonth tinyint
    set @InputMonth=DATEPART(month,@InputMoParam)

    --force all the dates to the first of the month
    set @InputMoParam=convert(varchar,@InputMonth)+'/1/'+datename(year,@InputMoParam)
    set @CurrentMoParam=convert(varchar,datepart(month,@Cu rrentMoParam))+'/1/'+datename(year,@CurrentMoParam)

    --do the testing
    if (datepart(month,@CurrentMoParam)=@InputMonth)
    print 'Current Month'
    else
    if (datepart(month,dateadd(day,-1,@CurrentMoParam))=@InputMonth)
    print 'Prior Month'
    else
    print 'Not Valid'

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Good catch.

    I would fix that problem by forcing everything to the first of the month. subtract a day, and force that to the first of the month, and then compare the entire date.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    Being i am using sql 2005 and do not have a date type how would i set the time to 12am on both variables?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It's not exactly the same, but I think it is actually better.
    Code:
    SELECT
       CASE DATEDIFF(month, @InputMoParam, @CurrentMoParam)
          WHEN 0 THEN 'CUrrent Month'
          WHEN 1 THEN 'PRIOR Mo'
          ELSE 'Not Valid'
       END
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2003
    Posts
    233
    Ding, the winner is ......

    Yes much better!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •