Month Function

(0) Share

Report

Posted on by Community Member

i have wrote this function for date...... used in daily cash accounting which is working perfectly, now i am going to develop monthwise cash accounting. can anyone please help me out how can i convert this date funtion to month.

DECLARE @OpeningAmt NUMERIC(19,2), @Pdate date

--return

SET @date= DATEADD(date, -1,@fromdate)

if (isnull(@FromDate, '1900-01-01') <> '1900-01-01')
begin
Declare @Opening table
(
TENDERTYPE int
, TenderName varchar(500)
, Opening numeric(19,2)
, NetSales decimal(28,2)
, DepositAmt numeric(19,2)
, ExpenseAmount numeric(19,2)
, Income numeric(19,2)
, Payments numeric(19,2)
)

insert into @Opening
EXEC [MtqRpt_TenderTotalsDynamic]
@PDataAreaID = @PDataAreaID,
@StoreId = @StoreId,
@FromDate = <10900-01-01>
@ToDate = @Pdate
@Terminal = @Terminal

select @OpeningAmt = sum( NetSales + Income - (DepositAmt + ExpenseAmount ) )
from @Opening
where TENDERTYPE = 1
end

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Jie G on at

Like (0)

Report

RE: Month Function

Hi，

If you mean how to get a date monthly, you can still use DATAADD, specifying the first argument as mm. Reference: docs.microsoft.com/.../dateadd-transact-sql

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report
RE: Month Function

Hi AXDYNAMICS,

To get beginning of the month you can use

SELECT DATEADD(DAY,1,EOMONTH(@mydate,-1))

and for end of the month

SELECT EOMONTH(@mydate)

Was this reply helpful? Yes No
André Arnaud de Cal... 300,289 Super User 2025 Season 2 on at

Like (1)

Report

RE: Month Function

How is this SQL script related to Microsoft Dynamics AX? What exactly should it do? If it is not related to Microsoft Dynamics AX, you can better use a SQL forum.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Month Function

what i will write for if condition where date used 1900-01-01?

Was this reply helpful? Yes No
André Arnaud de Cal... 300,289 Super User 2025 Season 2 on at

Like (2)

Report

RE: Month Function

I'm sorry, but I really don't understand what this question is about. The title has something in it called 'month'. You are on a Dynamics AX forum asking a question about possibly an SQL script and now you ask a question about a date on January 1, 1900? I'm lost here...

Was this reply helpful? Yes No