How to convert Netezza to_timestamp & tochar to SQL Server?

qvtsj1bj 于 2023-05-16 发布在 SQL Server

关注(0)|答案(4)|浏览(248)

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?

sql-server

来源：https://stackoverflow.com/questions/76229717/how-to-convert-netezza-to-timestamp-tochar-to-sql-server

4条答案

按热度按时间

jecbmhm31#

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:

Constructing valid DateTime value from the numeric input
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

赞(0）回复(0）举报 2023-05-16

pftdvrlh2#

You should use the time to convert your number column zto a proper datetime and only use that in future.

every convertion tale a lot of time, so date shpould always be stored as such datatypes

declare @input As numeric(17,0) = 20230324121023123

select 
  CONVERT(DATETIME,
  CAST(
  Substring(cast(@input as varchar), 0,9) + ' ' 
  + Substring(cast(@input as varchar),09,2)
  + ':' + Substring(cast(@input as varchar), 11,2)
  + ':' + Substring(cast(@input as varchar), 13,2)
  + ':' + Substring(cast(@input as varchar), 15,3)
   as datetime),120) dat

dat
2023-03-24 12:10:23.123

fiddle

赞(0）回复(0）举报 2023-05-16

sauutmhj3#

=> select 20230324121023123, TO_CHAR(20230324121023123,'9999-99-99 99:99:99"."999');
     ?COLUMN?      |         TO_CHAR
-------------------+--------------------------
 20230324121023123 |  2023-03-24 12:10:23.123

赞(0）回复(0）举报 2023-05-16

dnph8jn44#

To avoid using vendor specific functions (like, for SQL Server) and make this more portable across any data source you may be connecting Cognos to, this probably should have written like this in the first place:

cast(
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  1, 4) || '-' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  5, 2) || '-' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  7, 2) || ' ' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  9, 2) || ':' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 11, 2) || ':' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 13, 2) || '.' ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 15, 3)
  , timestamp
)

You can also do it similarly using numbers:

cast(
  cast(cast(    [POL].[PERIOD_FROM_DATE]/10000000000000     , int), varchar(4)) || '-' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/100000000000, 100 ), int), varchar(2)) || '-' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/1000000000  , 100 ), int), varchar(2)) || ' ' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/10000000    , 100 ), int), varchar(2)) || ':' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/100000      , 100 ), int), varchar(2)) || ':' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/1000        , 100 ), int), varchar(2)) || '.' ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]             , 1000), int), varchar(3))
  , timestamp
)

Here is an example Cognos report spec using the GO Sales (query) sample data source demonstrating this in data items expressions in a report. You would use the same expressions in Framework Manager.

<report xmlns="http://developer.cognos.com/schemas/report/15.5/" useStyleVersion="11.6" expressionLocale="en-us">
    <drillBehavior/>
    <layouts>
        <layout>
            <reportPages>
                <page name="Page1">
                    <style>
                        <defaultStyles>
                            <defaultStyle refStyle="pg"/>
                        </defaultStyles>
                    </style>
                    <pageBody>
                        <style>
                            <defaultStyles>
                                <defaultStyle refStyle="pb"/>
                            </defaultStyles>
                        </style>
                        <contents>
                            <list horizontalPagination="true" refQuery="Query1" name="List1">
                                <noDataHandler>
                                    <contents>
                                        <block>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <staticValue>No Data Available</staticValue>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                            <style>
                                                <CSS value="padding:16px;"/>
                                            </style>
                                        </block>
                                    </contents>
                                </noDataHandler>
                                <style>
                                    <CSS value="border-collapse:collapse"/>
                                    <defaultStyles>
                                        <defaultStyle refStyle="ls"/>
                                    </defaultStyles>
                                </style>
                                <listColumns>
                                    <listColumn>
                                        <listColumnTitle>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lt"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemLabel refDataItem="PERIOD_FROM_DATE"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnTitle>
                                        <listColumnBody>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lc"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemValue refDataItem="PERIOD_FROM_DATE"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnBody>
                                    </listColumn>
                                    <listColumn>
                                        <listColumnTitle>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lt"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemLabel refDataItem="PERIOD_AS_TIMESTAMP"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnTitle>
                                        <listColumnBody>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lc"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemValue refDataItem="PERIOD_AS_TIMESTAMP"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnBody>
                                    </listColumn>
                                    <listColumn>
                                        <listColumnTitle>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lt"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemLabel refDataItem="PERIOD_AS_TIMESTAMP1"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnTitle>
                                        <listColumnBody>
                                            <style>
                                                <defaultStyles>
                                                    <defaultStyle refStyle="lc"/>
                                                </defaultStyles>
                                            </style>
                                            <contents>
                                                <textItem>
                                                    <dataSource>
                                                        <dataItemValue refDataItem="PERIOD_AS_TIMESTAMP1"/>
                                                    </dataSource>
                                                </textItem>
                                            </contents>
                                        </listColumnBody>
                                    </listColumn>
                                </listColumns>
                            </list>
                        </contents>
                    </pageBody>
                    <XMLAttributes>
                        <XMLAttribute output="no" name="RS_legacyDrillDown" value="0"/>
                    </XMLAttributes>
                </page>
            </reportPages>
        </layout>
    </layouts>
    <XMLAttributes>
        <XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/>
        <XMLAttribute output="no" name="listSeparator" value=","/>
        <XMLAttribute output="no" name="decimalSeparator" value="."/>
        <XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/>
    </XMLAttributes>
    <queries>
        <query name="POL">
            <source>
                <sqlQuery name="SQL1" dataSource="great_outdoors_sales">
                    <sqlText>select cast(20230324121023123 as decimal(17,0)) as PERIOD_FROM_DATE</sqlText>
                    <mdProjectedItems>
                        <mdProjectedItem name="PERIOD_FROM_DATE"/>
                    </mdProjectedItems>
                </sqlQuery>
            </source>
            <selection>
                <dataItem name="PERIOD_FROM_DATE" aggregate="none" rollupAggregate="none">
                    <expression>[SQL1].[PERIOD_FROM_DATE]</expression>
                </dataItem>
            </selection>
        </query>
        <query name="Query1">
            <source>
                <queryRef refQuery="POL"/>
            </source>
            <selection>
                <dataItem aggregate="none" rollupAggregate="none" name="PERIOD_FROM_DATE">
                    <expression>[POL].[PERIOD_FROM_DATE]</expression>
                    <XMLAttributes>
                        <XMLAttribute output="no" name="RS_dataType" value="2"/>
                        <XMLAttribute output="no" name="RS_dataUsage" value=""/>
                    </XMLAttributes>
                </dataItem>
                <dataItem aggregate="none" rollupAggregate="none" name="PERIOD_AS_TIMESTAMP">
                    <expression>cast(
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  1, 4) || &apos;-&apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  5, 2) || &apos;-&apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  7, 2) || &apos; &apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)),  9, 2) || &apos;:&apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 11, 2) || &apos;:&apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 13, 2) || &apos;.&apos; ||
  substring(cast([POL].[PERIOD_FROM_DATE], varchar(50)), 15, 3)
  , timestamp
)</expression>
                    <XMLAttributes>
                        <XMLAttribute output="no" name="RS_dataType" value="4"/>
                        <XMLAttribute output="no" name="RS_dataUsage" value="0"/>
                    </XMLAttributes>
                </dataItem>
                <dataItem aggregate="none" rollupAggregate="none" name="PERIOD_AS_TIMESTAMP1">
                    <expression>cast(
  cast(cast(    [POL].[PERIOD_FROM_DATE]/10000000000000     , int), varchar(4)) || &apos;-&apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/100000000000, 100 ), int), varchar(2)) || &apos;-&apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/1000000000  , 100 ), int), varchar(2)) || &apos; &apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/10000000    , 100 ), int), varchar(2)) || &apos;:&apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/100000      , 100 ), int), varchar(2)) || &apos;:&apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]/1000        , 100 ), int), varchar(2)) || &apos;.&apos; ||
  cast(cast(mod([POL].[PERIOD_FROM_DATE]             , 1000), int), varchar(3))
  , timestamp
)</expression>
                    <XMLAttributes>
                        <XMLAttribute output="no" name="RS_dataType" value="4"/>
                        <XMLAttribute output="no" name="RS_dataUsage" value="0"/>
                    </XMLAttributes>
                </dataItem>
            </selection>
        </query>
    </queries>
    <classStyles>
        <classStyle name="GuidedLayoutLeftPadding">
            <CSS value="padding-left:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/>
        </classStyle>
        <classStyle name="GuidedLayoutTopPadding">
            <CSS value="padding-top:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/>
        </classStyle>
        <classStyle name="GuidedLayoutRightPadding">
            <CSS value="padding-right:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/>
        </classStyle>
        <classStyle name="GuidedLayoutBottomPadding">
            <CSS value="padding-bottom:5px;border-top-width:1px;border-bottom-width:1px;border-left-width:1px;border-right-width:1px"/>
        </classStyle>
        <classStyle name="GuidedLayoutMargin">
            <CSS value="margin-bottom:10px"/>
        </classStyle>
    </classStyles>
    <modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
</report>

赞(0）回复(0）举报 2023-05-16

我来回答

How to convert Netezza to_timestamp & tochar to SQL Server?

4条答案

相关问题

热门标签

最新问答