BigQuery timestamp does not support more than six decimals

Converting iso dates with timezones to BigQuery timestamp can be a hassle if the date contains more than six decimals.

select cast('2023-03-21T06:49:19.1234567+00:00' as timestamp)

Invalid timestamp: '2023-03-21T06:49:19.1234567+00:00'






One solution could be to truncate the fractional part like so

create or replace function [your_dataset].truncate_iso_timestamp_string(detailed_iso_timestamp string)
/* big query crashes with more than 6 decimals on dates
This function will truncate to 6 decimals and cast to timestamp. */
returns timestamp
as (
  cast(
    concat(
      left(
        split(detailed_iso_timestamp,'+')[offset(0)], 26),
        '+', 
        split(detailed_iso_timestamp,'+')[offset(1)]) as timestamp)
);



select [your_dataset].truncate_iso_timestamp_string('2023-03-21T06:49:19.1234567+00:00')



Comments

Post a Comment

Popular posts from this blog

How to decrypt stored password from SSMS registered servers

Image
Right click your server, choose tasks/export Uncheck "Do not include user names and passwords in the export file" Save the export to a file Open the file in an editor and locate the tag RegisteredServers:ConnectionStringWithEncryptedPassword Copy the contents of password attribute Open powershell and paste the code below, after you amend it with your encrypted string. $base64pass = "PasteEncryptedPasswordHere" [System.Reflection.Assembly]::LoadWithPartialName("System.Security") | Out-Null [System.Text.Encoding]::Unicode.GetString([System.Security.Cryptography.ProtectedData]::Unprotect([System.Convert]::FromBase64String($base64pass), $null, [System.Security.Cryptography.DataProtectionScope]::CurrentUser))
Read more

Cause for Parameter is incorrect 0x80070057 error in ssis

Image
Possible cause for the cryptic error The package failed to load due to error  0x80070057 "The p arameter is incorrect.". This occurs when CPackage::LoadFrommXML fails.  ...when opening or executing and SSIS package In my case i created the ssis package using biml. And accidentally created a duplicate precedence constraint to another task twice. Removing the duplicate constraint and re-generating the package solved it.
Read more

How to fail sql server agent job if powershell script fails

Image
Problem 1 I tried adding a powershell script to a sql server agent job step, but there are some severe limitations where write-host or write-verbose outputs are not allowed instead of ignored, which should make sens. Then the job crashes with the following error:  A job step received an error at line [xx] in a PowerShell script. The corresponding line is 'write-host "test";  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows.  '.  Process Exit Code -1.  The step failed. Problem 2 So instead I created a Operating System (CmdExec) step with calling pow...
Read more