Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Walt1010
Helper III
Helper III

Update the time part of a datetime value

I'd like to update the date part of a datetime value (eg

from 

08/07/2024 09:00:00)

 

to midnight 

08/07/2024 24:00:00

 

but I'm not sure if its just easier to extract the date and create a new datetime value, or to update the time in an existing value, and whether I have to do various conversions. Any ideas?

1 ACCEPTED SOLUTION

you can try this in PQ

 

=DateTime.From( Date.From([column]))

11.PNG

 

or 

 

=DateTime.From(Date.AddDays( Date.From([column]),1))

 

12.PNG

 

if you don't want the original column , you can remove it in the end





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Kedar_Pande
Resident Rockstar
Resident Rockstar

You can write the below DAX:

 

NewDateTime = DATE(YEAR([DateTime]), MONTH([DateTime]), DAY([DateTime])) + TIME(0, 0, 0)

 This expression extracts the date portion from your existing DateTime field and resets the time to midnight. You can replace [DateTime] with the actual column name in your dataset.

 

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly. Your Kudos/Likes are much appreciated!

 

Regards,

Kedar Pande

www.linkedin.com/in/kedar-pande

ryan_mayu
Super User
Super User

@Walt1010 

you can to change all the time to midnight? or only 9 am?

what's more, we don't have 24 for hour, should that be 00:00:00?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I want to change the time to midnight in all cases. Sorry I didn't realise it should be 00:00:00. I actually need to show midnight datetime as the next day's date plus 00:00:00.

you can try this in PQ

 

=DateTime.From( Date.From([column]))

11.PNG

 

or 

 

=DateTime.From(Date.AddDays( Date.From([column]),1))

 

12.PNG

 

if you don't want the original column , you can remove it in the end





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thats great. Many thanks!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.