locked
change text data into regular date RRS feed

  • Question

  • I have a column that has text string for dates like this Apr 9, 2016 2:00:17 AM PDT. When I change to date it gives me an error. How can I fix this? 
    Wednesday, March 8, 2017 7:49 PM

Answers

  • Power Query can convert various formats to a date, but it won't recognize the PDT part.

    You can get rid of this via Transform ... Extract ... First Characters (just enter a number) and adjust the code using Text.Length (see below).

    Next you can parse the date from the string. This works with culture code "en-US" (I added this explicitly in the code below after recording this video):

    let
        Source = "Apr 9, 2016 2:00:17 AM PDT",
        #"Converted to Table" = #table(1, {{Source}}),
        #"Extracted First Characters" = Table.TransformColumns(#"Converted to Table", {{"Column1", 
         each Text.Start(_, Text.Length(_) - 4), type text}}),
        #"Parsed Date" = Table.TransformColumns(#"Extracted First Characters",{{"Column1", each Date.From(DateTimeZone.From(_, "en-US")), type date}})
    in
        #"Parsed Date"

    Wednesday, March 8, 2017 8:37 PM
  • SELECT CONVERT(datetime, SUBSTRING(CONVERT(varchar(30), [YourCol]), 1, LEN(CONVERT(varchar(30), [YourCol])) - 4), 101)
      FROM [dbo].[YouTable] 


    A Fan of SSIS, SSRS and SSAS

    Wednesday, March 8, 2017 11:28 PM
    Answerer

All replies

  • Hi,

    DATE standard format is yyyy-mm-dd, so you need to apply some string manipulation before converting Apr 9, 2016 2:00:17 AM PDT into 2016-04-09.

    Wednesday, March 8, 2017 8:14 PM
  • Power Query can convert various formats to a date, but it won't recognize the PDT part.

    You can get rid of this via Transform ... Extract ... First Characters (just enter a number) and adjust the code using Text.Length (see below).

    Next you can parse the date from the string. This works with culture code "en-US" (I added this explicitly in the code below after recording this video):

    let
        Source = "Apr 9, 2016 2:00:17 AM PDT",
        #"Converted to Table" = #table(1, {{Source}}),
        #"Extracted First Characters" = Table.TransformColumns(#"Converted to Table", {{"Column1", 
         each Text.Start(_, Text.Length(_) - 4), type text}}),
        #"Parsed Date" = Table.TransformColumns(#"Extracted First Characters",{{"Column1", each Date.From(DateTimeZone.From(_, "en-US")), type date}})
    in
        #"Parsed Date"

    Wednesday, March 8, 2017 8:37 PM
  • Hello, 

    If your dataset is not too big another option would be to create a calculated column and do a LEFT() function like in excel. In your case, the formula will be: 

    Table1[GoodDateColumn] = LEFT([BadDateColumn], 11) 

    then convert it into date format or format it with a nested FORMAT() function.

    Thank you, N  -  





    Wednesday, March 8, 2017 10:47 PM
  • SELECT CONVERT(datetime, SUBSTRING(CONVERT(varchar(30), [YourCol]), 1, LEN(CONVERT(varchar(30), [YourCol])) - 4), 101)
      FROM [dbo].[YouTable] 


    A Fan of SSIS, SSRS and SSAS

    Wednesday, March 8, 2017 11:28 PM
    Answerer
  • Hi Alex,

    Have any of the presented solutions helped to solve your problem?


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Sunday, April 2, 2017 5:56 PM
  • Hello AlexMartini,

    See below query.You need to handle date conversions for 3 different scenarios based on incoming string length.

    Declare @dt varchar(50) ='Apr 9, 2016 2:00:17 AM PDT'
    --select len(@dt)
    --select left(@dt,24)
    select 
        case when len(@dt) = 28 then cast(left(@dt,24) as datetime) 
        when len(@dt) = 26 then cast(left(@dt,22) as datetime) 
        when len(@dt) = 27 then cast(left(@dt,23) as datetime) 
    end as Dt

    Let us know if you have any questions.

    Please Mark this reply as answer if it has solved your issue. Or please use Vote As Helpful if my post was useful so that other forum members can benefit from it.

    Sunday, April 2, 2017 8:23 PM