sqlsql-servernetezzacognos

How to convert Netezza to_timestamp & tochar to SQL Server?

I want to convert Netezza

TO_TIMESTAMP(TO_CHAR(POL.PERIOD_FROM_DATE, 'FM99999999999999999'), 'YYYYMMDDHH24MISSMS') AS EFFECTIVE_DATE

to a SQL Server function to include this in IBM Cognos Framework Manager

Required output: 2023-02-24 00:00:00.0

Can anyone please help me with this?

Solution

  • It looks like PERIOD_FROM_DATE is a numeric(17,0) column, with each digit matching a position in a datetime value, down to the 1000th of a second.

    This was a poor schema choice, but I understand you may have no say here. Additionally, the Oracle code handles this in about the worst way possible. Thanks to cultural/internationalization issues, converting between strings and numeric or date values is not the simple process we often assume; rather it's about the slowest and most error-prone approach you can take.

    Assuming the schema is set in stone (but really: change it if you can), to solve this for SQL Server in a better way we'll break it into two parts:

    1. Constructing valid DateTime value from the numeric input
    2. Outputting the desired string format given the datetime value from part 1

    Note: you're generally much better off simply returning a raw DateTime, and letting the client code or tooling handle the format. This lets you skip part two completely, and it's what the Oracle code was doing. That you saw a specific string format was an accident of the tooling, as the result returned from Oracle was binary and not a human-readable value at all.

    Again, I'll assume you can't control this requirement, but to the degree you have the ability you should push to fix this (because the current design really is broken).

    For part one, we want to use the DATETIMEFROMPARTS() method:

    DATETIMEFROMPARTS(
   cast(POL.PERIOD_FROM_DATE/10000000000000 as int),      -- year
   cast(POL.PERIOD_FROM_DATE/100000000000  % 100 as int), -- month
   cast(POL.PERIOD_FROM_DATE/1000000000  % 100 as int),   -- day
   cast(POL.PERIOD_FROM_DATE/10000000  % 100 as int),     -- hour
   cast(POL.PERIOD_FROM_DATE/100000  % 100 as int),       -- minute
   cast(POL.PERIOD_FROM_DATE/1000  % 100 as int),         -- second
   cast(POL.PERIOD_FROM_DATE  % 1000 as int)              -- milliseconds
)

    It seems like you might only want the first digit from the milliseconds. If so, you can adjust that portion accordingly as I don't have enough information to know whether to round or truncate the remainder.

    This may look like a lot of code and separate access to the column, but I promise you it should be faster and more reliable than the string alternative, and not by a small margin. It does still assume the data is stored consistently.

    Once you have this DateTime value, you can get a formatted string using either CONVERT() or FORMAT(), with the former preferred when possible. Unfortunately, none of the built in formats use a single digit with the fractional seconds, so you will need to do this:

    FORMAT(DATETIMEFROMPARTS(
   cast(POL.PERIOD_FROM_DATE/10000000000000 as int),      -- year
   cast(POL.PERIOD_FROM_DATE/100000000000  % 100 as int), -- month
   cast(POL.PERIOD_FROM_DATE/1000000000  % 100 as int),   -- day
   cast(POL.PERIOD_FROM_DATE/10000000  % 100 as int),     -- hour
   cast(POL.PERIOD_FROM_DATE/100000  % 100 as int),       -- minute
   cast(POL.PERIOD_FROM_DATE/1000  % 100 as int),         -- second
   cast(POL.PERIOD_FROM_DATE  % 1000 as int)              -- milliseconds
), 'yyyy-MM-dd HH:mm:ss.f')

    See it work here:

    https://dbfiddle.uk/W4wv24YG