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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ericOnline
Post Patron
Post Patron

BUG: Changing String to Date/Time = bad data

Hello, 

 

Just noticed this.

 

Issue:

  • I'm in Pacific Timezone (currently on Standard Time which is UTC-7).
  • I have date data that imports as a STRING such as "2017-11-01T12:00:00.000Z".
  • When I change the data type to Date/Time, the new value of the column is "11/1/2017 5:00:00 AM".
  • It should be "10/31/2017 5:00:00 AM".
  • This action ADDS 8 hours to UTC instead of SUBTRACTING it.

Unless I'm way off here! Which is a possibility!

Thank you

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Explain again why converting:

 

2017-11-01T12:00:00.000Z

 

Which is clearly November 1st, 2017 should convert to October 31st again?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Explain again why converting:

 

2017-11-01T12:00:00.000Z

 

Which is clearly November 1st, 2017 should convert to October 31st again?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler. I messed up! I was reading the string 2017-11-01T12:00:00.000Z, as ...12:00:00AM. After changing data type to Date/Time, the new value, of 11/01/2017 05:00:00AM looked like "midnight minus 7 hours" but still on the same day (rather than moving to the previous day).

 

What I should have realized, is that ...12:00:00 is midday on a 24hr. time scale, NOT midnight. My mistake. 

 

Thank you for the reply!

On this note though... how do I control the translation when moving from a STRING to DATE/TIME data type? I ask because this particular column of data has time info from MULTIPLE timezones, but the translation is blanket. It changes ALL times to my local computers time.

 

Thank you

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.