Function Inquiry - passing a column date to create a new column date, multiple times

[KHart]

I have limited access to some tools in Spotfire due to my user configuration as deemed by my business. I am not well versed in python, R, etc.

I have a scalar valued function I have in SQL, but the performance is poor when I pass through an information link. So I'm trying to look as to what my options are as opposed to making 3-4 calculated column per date, just to get my final result.  Example, system stores the date as UTC, which I want to keep in the data table, but I also need to calculate UTC to CST/CDT for all dates I have in the data table. 

Where can I go about researching my options? I feel like a data function solves this but I'm not sure where to start.

[David Boot-Olazabal]

Hi KHart,

This may be a possible solution for the conversion of the time zone differences: https://community.spotfire.com/forums/topic/1203-how-do-i-make-a-visualization-dynamically-change-the-datetime-value-based-on-the-devices-time-zone-setting/#comment-1203.

You could also adapt the SQL statement from the Information Link, using SQL Server's AT TIME ZONE function. The SQL code could be looking like this:

SELECT SalesOrderID, OrderDate,   
    OrderDate AT TIME ZONE 'Eastern Standard Time' AS OrderDate_TimeZoneEST,  
    OrderDate AT TIME ZONE 'Eastern Standard Time'   
    AT TIME ZONE 'UTC' AS OrderDate_TimeZoneUTC  
FROM Sales.SalesOrderHeader; 

.

Kind regards,

David

Edited April 8 by David Boot-Olazabal

[KHart]

Hi David,

Thank you, I'll take a look at the link.

I have explored the SQL statement option like you mentioned in the past but didn't suit business needs. My conundrum is I have a list of locations, not all of which observe daylight savings time. So each individual location has to report out on that location's respective time zone. This means mixing and matching time zone information in the end data set for the business (reporting EST, CST, DST, AST, etc in the same report). The end result should be all the locations in a single report have different time zones, which we just refer to as a location's local time zone.