Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Convert Text to Date in Direct Query mode

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.

Need Help
  • Tahreem24's avatar
    Tahreem24
    6 years ago

    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!

2 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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