# Thread: Current month prior month function

1. Registered User
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. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
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
print 'Prior Month'
else
print 'Not Valid'```

3. Registered User
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
print 'Prior Month'
else
print 'Not Valid'

4. Registered User
Join Date
Sep 2001
Location
Chicago, Illinois, USA
Posts
603
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.

5. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

7. Registered User
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
•