Convert VARCHAR to DATE - Please help!!!

[lorenmario]

I am creating a report in Report Studio, accessing a SQL Server 2008 data source.

I need to create a filter that returns only the last period of the field Month_Ref. The problem is that the field is of type VARCHAR, ex.: 06/2013.
How do I convert this field to date?
After converting,  which function I can use to return the most recent month?

Thank you so much!

[Rosanero4Ever]

Hi,
If I'm well understanding your question, you can try this:
cast('01/' + Month_Ref;date)

It couldn't work because the presence of /. So, you could replace / with -.

Tell me if it works, please ;-)

[lorenmario]

Hi Friend, thanks for the reply.

It did not work ... I tried to do so and did not work:

convert(date, [Camada Lógica].[Dim2 Calculo Modelos].[Month Ref] , 111)

The field I am trying to convert has only month and year, in style YYYY / MM.

Any idea?

Tks!!!

[Rosanero4Ever]

If you convert to date type I think you must include the day information
For this reason I added the '01'  dummy string ;)
I hope this help you

[lorenmario]

It worked!

Now I need to use a function that returns always the last sentence, you know what function I can use?

Thank you so much!
;)

[MFGF]

Now I need to use a function that returns always the last sentence, you know what function I can use?

I'm not sure what you're looking for here. What do you mean by "returns always the last sentence"?

MF.

[Michael75]

You have your reformatted date field as an item in a query, right?

One solution is to use Maximum as the Aggregate Function. This will return the most recent month.

[lorenmario]

Hi friend, is the following:
My date field (YYYY/MM/DD) returns the following dates:

2013/01/01
2013/02/01
2013/03/01
2013/04/01
2013/05/01
2013/06/01

I need a function that returns me only the most recent month, okay? The last month of this field.

Tks!!! :)

[MFGF]

As Michael75 suggested... use the maximum() summary in your expression to return the most recent entry.

Cheers!

MF.

[lorenmario]

Hi friend,
When do I create a query calculation works correctly, but this field that will return the last month should not appear in the report, I just need to create a filter with it.
I tried to insert a filter like this:

maximum ([Year_Month_Ref])

But it did not work ... gives error.

Can you tell me why? The maximum function does not work as a filter?!

Thank you so much!

[MFGF]

Hi friend,
When do I create a query calculation works correctly, but this field that will return the last month should not appear in the report, I just need to create a filter with it.
I tried to insert a filter like this:

maximum ([Year_Month_Ref])

But it did not work ... gives error.

Can you tell me why? The maximum function does not work as a filter?!

Thank you so much!

All filters need to evaluate to a TRUE or FALSE result, so the expression you used is not valid for a filter. Try this instead:

[Year_Month_Ref] = maximum([Year_Month_Ref] for report)

Cheers!

MF.

[lorenmario]

Hello, good morning!

It worked! What a relief!

Thank you for your help!!
:)