CODEWITHSUNDEEP
csvdategoogle-bigquerytableau-api
scotti
scotti

Reputation: 39

Tableau Timestamp String to Date

i have a timestamp in my bigquery looking like this: 2017.09.25 10:22:19 i want to convert this string to a date dimension. i tried it with the dropdown menu, calculated fields like datetime, dateparse, date,... and a calculated field where i trimmed the string and took only parts of the date as a sum, but nothing is working. I always get the error that google bigquery could'nt compile my task: "Invalid date: '2017.07.03 10:52:16' "

does anyone have an idea as a solution for my problem?

regards

Upvotes: 0

Views: 872

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Date parts need to be separated with dashes, not dots, in order for the cast to work. For example,

'2017-09-25 10:22:19'

As a string, this is valid to cast both to a DATETIME and a TIMESTAMP type. If you want to convert your original string to one of these types, however, you can use PARSE_DATETIME, or similarly PARSE_TIMESTAMP:

SELECT
  PARSE_DATETIME('%Y.%m.%d %T', timestamp_string) AS datetime
FROM YourTable;

Upvotes: 2

Related Questions