Areas of Interest, as counted by my cat

Month: September 2016

Converting a TIMESTAMP WITH TZ in UTC to Central

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:

select
ID,
ONLINE_ENROLLMENT_TIME,
(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
from ZYXX.D_Person
where ONLINE_ENROLLMENT_TIME
is not null;

Results:

image

Testing using SQL and common table expressions

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:

with 
myobject as (
-- put in a range of test values:
select 'dbo.MyFunction' as NAME
union
select 'setup.ins_Contact'
union
select 'MyOtherFunction'
),
more2 as (
-- calculate interesting attributes:
select
NAME,
len( NAME ) as LENGTH_,
charindex( '.', NAME ) as POSITION
from myobject
)
-- perform the test:
select
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_
from more2;
;

© 2020 More Than Four

Theme by Anders NorenUp ↑