cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhol Member
Member

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)

 

[Date].PNG

 

 

How it can be done?

 

Thanks!

Nir

 

1 ACCEPTED SOLUTION

Accepted Solutions
nhol Member
Member

Re: Seperate Time from Date

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

4 REPLIES 4
Moderator v-qiuyu-msft
Moderator

Re: Seperate Time from Date

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: 

 

a1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nhol Member
Member

Re: Seperate Time from Date

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

 

test.png

 

Any other workaround?

 

Thanks!

Nir

Moderator v-qiuyu-msft
Moderator

Re: Seperate Time from Date

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: 

 

q4.PNG

 

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

 

q5.PNG

 

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

 

q6.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nhol Member
Member

Re: Seperate Time from Date

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