topic Re: Convert date in date type, not string in Report Server

Convert date in date type, not string

giuliapiazza94 — Tue, 05 Nov 2024 15:21:24 GMT

Hi guys,

i try to use report builder but i have some problems with data type.

I've this query

When i try to filter date (for example date >= 01/01/2024), I can't to do this because it convert data in string. In power bi the field is date type, not string.

How can I convert the column in date type?

Thank you all 🙂

Re: Convert date in date type, not string

FarhanJeelani — Tue, 05 Nov 2024 16:10:59 GMT

In Report Builder, if the date field `[Data]` is recognized as text, you can try explicitly converting it to a date format within your query or by using an expression in the filter. Here are two methods to achieve this:

Method 1: Modify the Query to Cast `[Data]` as a Date
If you have control over the query, try casting or converting the `[Data]` column to a date format directly in the query. The syntax will depend on your data source.

For example:
- SQL Server:

sql

SELECT CAST([Data] AS DATE) AS [Data] FROM your_table

- DAX (if using Power BI):
DAX

EVALUATE SELECTCOLUMNS( your_table, "Data", DATEVALUE([Data]) )

This approach ensures that `[Data]` is treated as a date field within Report Builder.

Method 2: Use an Expression in Report Builder to Convert Text to Date
If you cannot modify the query, use an expression in the Filter properties to convert `[Data]` to a date type before applying the filter:

1. In the filter properties, select `[Data]` as the Expression.
2. Change the data type to **Date/Time instead of Text.
3. In the Value field, enter your date filter in the appropriate format (e.g., `1/1/2024` or `01/01/2024` depending on your locale).

Alternatively, if you need more complex date filtering in Report Builder, you can use an expression like:
VB
=CDate(Fields!Data.Value)
This expression will convert `[Data]` to a date type, allowing you to apply date comparisons directly.

Let me know if you encounter any issues with these methods!

Re: Convert date in date type, not string

Ankur04 — Fri, 08 Nov 2024 07:33:30 GMT

Hi giuliapiazza94 ,

can you try using Cdate function in the expression.

please let me know if you need further support.

Thanks,

Re: Convert date in date type, not string

Anonymous — Mon, 25 Nov 2024 08:30:50 GMT

Hi, giuliapiazza94

Based on your information, I create a sample Table:

Import to the Report Builder. Then right-click dataset.

In the query designer, you can use expressions to convert strings to date types. For example, if your date string is in the format "dd/MM/yyyy", you can use the following expression:

CONVERT(date, [DateString], 103)

Here, 103 is the style code used for the UK/France date format.

Converted query:

Insert a Table visual and click Run

If you want to use Cdate, you can right click text, select Expression.

Use the following code:

=CDate(Fields!DateString.Value)

Before:

After:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.