Forum Discussion

nhol's avatar
nhol
Advocate II
8 years ago
Solved

Seperate Time from Date

I have a [Date] column which includes Date and Time.

I would like to extract the time to have it in a seperate column as well as the date BUT Still keeping the original [Date] column.

I created a copy of the [Date] column and tried to apply a simple format. This worked until I had to use it in an advanced filtering and apparently it still kept the date although it is not shown (because of formatting).

(the table name is MRR_IR_Paper)

 

 

 

How it can be done?

 

Thanks!

Nir

 

Data Modeling
  • nhol's avatar
    nhol
    8 years ago

    Thanks so much!

    That helped although I was not able to use it in a visual because it can not accept a measure as Axis.

    I think I should go ahead and add another field in the database.

     

    Best,

    NH

5 Replies

    • nhol's avatar
      nhol
      Advocate II
      8 years ago

      Not sure what I'm doing wrong but PowerBI keeps throwing an error message:

      Maybe it is something related to the fact I'm on DirectQuery mode, which is must in my case as I need real time data in my model

       

       

      Any other workaround?

       

      Thanks!

      Nir

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        8 years ago

        Hi nhol,

         

        As you are using DirectQuery mode, please create a measure rather than calculated column. To make sure Format() function works, please enable below option: 

         

         

        Measure = FORMAT(MAX([CreateTime]),"hh:mm:ss")

         

         

        Or, assume you connect to SQL Server database, you can write T-SQL query to get data: 

         

         

         

        Best Regards,
        Qiuyun Yu

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You can go to the Query editor ("Transform data"), then the "Add column" tab, then "Time" in the panel "From date & time":