Convert Text to Date in Direct Query mode

[Anonymous]

Hi, I'm using DirectQuery mode for my report. I have date fields which are of the data type Text.

I tried converting it into Date by changing the Data Type from Text to Date. It says "This step results in a query that is not supported in DirectQuery mode." I get the same error when I try to add a custom column (Month = Date.FromText([DateField])).

How do I get the month number, month name, and year from the text field? How do I convert the text field into a date field in DirectQuery mode? The existing solutions do not seem to work in DirectQuery mode.

Please help! Thanks.

[Tahreem24 · Accepted solution]

@Anonymous ,

While connecting to direct query mode you can use native query editor to transform your columns.

CONVERT(datetime, YourDatecolumn)

Don't forgrt to hit THUMBS UP and Accept this as a solution if it helps you!

[Anonymous]

@Tahreem24Thank you!

For others having the same issue,

Use the following SQL command while getting data for your report:

SELECT CONVERT(date, [DateSubmitted]) as [DateSubmitted]
,DATEPART("MONTH", [DateSubmitted]) as [Month]
,DATEPART("YEAR", [DateSubmitted]) as [Year]
,(DATEPART("WEEK", [DateSubmitted]) as [Week]
,'Q' + CAST(DATEPART("QUARTER", [DateSubmitted]) as varchar) as [Quarter]
FROM..