Hi,
I am having a issue with date conversion in one of my report. my database is SQL server.
I have year_date column in my query which data type as varchar. that column show data like 2017-06, 2017-05.
I need to get the report based on year_date prompt. if I run the report for 2017-06
report needs to show data for previous month and current month. that is 2017-05, 2017-06
so I am trying to give my filter as:
[year_date] between _add_months (?MONTH?, -1) AND ?MONTH?
but it was throwing a datatype conversion error.
can anyone please help me how to convert my [year_date] column as date and how to use it in the filter.
thanks in advance.
2017-06 is not a date, so _add_months() will not work with that as an argument.
Assuming your parameter is named ?yearmo?, your filter will look like this:
[year_date] between
cast(
year(
_add_months (
cast(?yearmo? + '-01', date),
-1
)
),
varchar(4)
) +
'-' +
right(
'00' +
cast(
datepart(
{m},
_add_months (
cast( ?yearmo? + '-01', date),
-1
)
),
varchar(2)
),
2
)
and
?yearmo?
Thank you Dougp,
your code worked perfectly as I was expecting the results.
thank you very much for your quick help/reply
Hi dougp,
I know I am not perfect with sql server date functions, one more dumb question please help me
in my another query I have to get the discharges based on YTD. YTD starts from 09-01.
to get YTD count I have to use [cost year] column this is also string data type not a date data type. this column values are 09-01 etc..
when I run the report for currnet month2017-06 I have to get the discharges from
09-01 to previous month which is (2017-05) and
09-01 to current month which is (2017-06)
please let me know the filter code using [cost year] and [year_date]
your help greatly appreciated
Thank you in advance..