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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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