At ABACAB we have - very correctly - a TIMESTAMP WITH TIME ZONE column called ONLINE_ENROLLMENT_TIME that has been populated - via an ETL process - with values in UTC.
This is good, because it is a completely unambiguous value. Oh, if only all our date times were stored this way.
Unfortunately, the convention up until now has been:
- assume date times are in Central time zone
- convert to integer "date key" and "time key" data types (primary key for date and time dimensions).
This loses any time zone information, which is not great. It means that all client processes using this data also need to assume Central time zone.
The following SQL takes our time zone-aware column and transforms it:
(ONLINE_ENROLLMENT_TIME at time zone 'US/Central') as ONLINE_ENROLLMENT_TIME_C,
trunc( cast( (ONLINE_ENROLLMENT_TIME at time zone 'US/Central') as DATE )) as ONLINE_ENROLLMENT_DATE
is not null;
Sometimes I forget how to do simple things, like splitting a string into sections based on a delimiter.
Here's a nice way to test your code on a range of input values, and see the interim results, in one hit:
myobject as (
-- put in a range of test values:
select 'dbo.MyFunction' as NAME
more2 as (
-- calculate interesting attributes:
len( NAME ) as LENGTH_,
charindex( '.', NAME ) as POSITION
-- perform the test:
NAME, POSITION, LENGTH_,
case when POSITION=0 then 'dbo'
else substring( NAME, 1, POSITION-1 )
end as SCHEMA_,
substring( NAME, POSITION+1 , (LENGTH_ - POSITION ) ) as OBJECT_