More Than Four

Areas of Interest, as counted by my cat

Month: September 2016

Converting a TIMESTAMP WITH TZ in UTC to Central

September 7, 2016 / / 0 Comments

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

September 3, 2016 / / 0 Comments

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;
;

© 2024 More Than Four

Theme by Anders Noren — Up ↑