CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedurestype-conversion
axlrose89
axlrose89

Reputation: 37

Dynamic date parameter in stored procedure

i am trying to pass first day and last day of previous month as a date parameter in my stored procedure but it's throwing data conversion error like below:

Conversion failed when converting date and/or time from character string.

Here's example how i passed the parameter;

EXEC DBO.usp_PRODUCTS 'DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)'
                            ,'DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)'

The data type for start date and end date in my stored proc is date , even if i change to varchar, it's showing similar error.

Is there any way i can pass first day and last day of previous month as parameter in my stored proc?

Upvotes: 0

Views: 2681

Answers (2)

Eid Morsy
Eid Morsy

Reputation: 966

Try this

Declare @startDate Date = DATEADD(month, DATEDIFF(month,0,GETDATE())- 1, 0);
Declare @endDate   Date = DATEADD(month, DATEDIFF(month,0,GETDATE())   , 0);

EXEC DBO.usp_PRODUCTS @startDate , @endDate  ;

Upvotes: 1

chrisuae
chrisuae

Reputation: 1112

If you are using SQLServer 2012 or above, you can use the EOMonth function to return the last day of the month with an offset of -1 for the previous month:

EXEC DBO.usp_PRODUCTS 
    DateAdd(day,1,EOMonth(GetDate(),-2)),
    EOMonth(GetDate(),-1)

Upvotes: 0

Related Questions