SQL sever date column convertion from varchar to date

[cognos74]

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.

[dougp]

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:

Code Select Expand


[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?


[cognos74]

Thank you Dougp,

your code worked perfectly as I was expecting the results.
thank you very much for your quick help/reply

[cognos74]

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..