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

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

Hi nhol,

 

You can create a calculated column below: 

Time = FORMAT([Date],"hh:mm:ss")

 

Set the data type of this new column as Time data type: 

 

 

Best Regards,
Qiuyun Yu

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

    • nhol's avatar
      nhol
      Advocate II
      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