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
mcflurry
Helper I
Helper I

Convert string to date

Hello guys,

 

I have a string column with this:

 

20220307-06:30:06-GMT+0100

 

I'm trying to convert it to a date column without success.

How could it be done?

2 ACCEPTED SOLUTIONS
vojtechsima
Memorable Member
Memorable Member

Hi, @mcflurry 
In Power Query with TimeZone:

vojtechsima_0-1646660276179.png

 

 

 

let
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
Time = Time.FromText(Text.BetweenDelimiters([String], "-", "-")),
getTimeZone = Text.AfterDelimiter([String], "T"),
getPlusOrMinusZone = Text.Start(getTimeZone, 1),
GetHourOfZone = Number.FromText( Text.Middle(getTimeZone,1,2)),
GetMinuteOfZone = Number.FromText(Text.End(getTimeZone,2)),
DateTime = DateTime.From(Date & Time),
DateTimeZone = DateTime.AddZone(DateTime,GetHourOfZone,GetMinuteOfZone),
CompleteWithCheck = if getPlusOrMinusZone = "+" then DateTimeZone else DateTime.AddZone(DateTime,-GetHourOfZone,GetMinuteOfZone)

in CompleteWithCheck

 

 

Add New Column like this. "String" is your column with the provided date. Then just Change Type of the new column to "date" or "datetime" or "datetimezone".

Edit:
I enhanced the code to check whether it's "+" or "-".

View solution in original post

Regional Settings is already to an European country.

 

Could you please help me on how to fix it with your code?

I guess it's this line, but i don't have the knowledge to achieve it... 

 

Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),

 

I'm transforming the the original column with unicode format... and maybe it will work.

Yep, fixed that way, thanks.

View solution in original post

4 REPLIES 4
vojtechsima
Memorable Member
Memorable Member

Hi, @mcflurry 
In Power Query with TimeZone:

vojtechsima_0-1646660276179.png

 

 

 

let
Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),
Time = Time.FromText(Text.BetweenDelimiters([String], "-", "-")),
getTimeZone = Text.AfterDelimiter([String], "T"),
getPlusOrMinusZone = Text.Start(getTimeZone, 1),
GetHourOfZone = Number.FromText( Text.Middle(getTimeZone,1,2)),
GetMinuteOfZone = Number.FromText(Text.End(getTimeZone,2)),
DateTime = DateTime.From(Date & Time),
DateTimeZone = DateTime.AddZone(DateTime,GetHourOfZone,GetMinuteOfZone),
CompleteWithCheck = if getPlusOrMinusZone = "+" then DateTimeZone else DateTime.AddZone(DateTime,-GetHourOfZone,GetMinuteOfZone)

in CompleteWithCheck

 

 

Add New Column like this. "String" is your column with the provided date. Then just Change Type of the new column to "date" or "datetime" or "datetimezone".

Edit:
I enhanced the code to check whether it's "+" or "-".

Hello @vojtechsima 

I have a problem with your code.

It looks like it's taking the day as month and the month as day, and imported data from yesterday fails, i've tried with 02/28 as an example:

 

mcflurry_0-1647348385236.png

 

Hi, @mcflurry 
Go to Settings - Regional Settings (current file) and change it to some European country, in my case Czech Republic, or rewrite the script that it first writes month and then day.

Regional Settings is already to an European country.

 

Could you please help me on how to fix it with your code?

I guess it's this line, but i don't have the knowledge to achieve it... 

 

Date = Date.FromText(Text.BeforeDelimiter(Text.ReplaceRange([String],8,1,"T"),"T")),

 

I'm transforming the the original column with unicode format... and maybe it will work.

Yep, fixed that way, thanks.

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

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.