Data Integration Studio and Netezza, problem changing DATE9 to DATETIME19

[EinarRoed]

Hello,

This question is in regards to Data Integration Studio, which there doesn't seem to be a forum for.

 I have a basic DATE9 variable, which I need to transform to DATETIME19. Specifically, the value "21SEP2017" should become "21SEP2017:23:59:59".

A dirty way to do it is to use this expression: (DATE_DT *24*60*60)+86399

However, it's important that all temporary tables are stored in a Netezza library. For the sake of efficiency, this node should also be set to "Create as view". Unfortunately that makes it no longer accept the expression ("ERROR: The create view statement cannot be executed directly against the database because of errors").

Do you have any advice on a "Netezza-view-friendly" way to transform the date into datetime?

[Patrick]

@EinarRoed

The SAS Data Management forum is focused on tools like DIS.

https://communities.sas.com/t5/SAS-Data-Management/bd-p/data_management

As for your question: It looks to me like you're confusing here two concepts.

SAS stores Date and DateTime values as numbers in a numeric variable. The formula you show - DATE_DT *24*60*60 - is a way to convert a SAS Date value to a SAS DateTime value by multiplying the SAS Date value with the number of seconds of a day.

The additional bit - +86399 - looks like an attempt to shift the starting point of such date counts from the SAS to the Netezza value. That's certainly nothing you should try to attempt.

The SAS/Access engine will convert the dates for you. The mapping between SAS Date/Datetime and Netezza column types is documented here: 

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p07ge3f7fvfh5ln1msgdpqhbeh31.htm&docsetV...

Because SAS doesn't have its own data types for Dates and Times the conversion used is based on the SAS format assigned to a variable.

Make sure you have a format of DATEw. assigned to variables holding SAS Date values and a format of DATETIMEw.d for SAS DateTime values. If you do so then things should just work.  

[LinusH]

This is actually a quit interesting question.

If I understand it right, both source and target in your job is within Netezza, and you wish to perform ELT, to avoid data to pass the "remote" SAS session, right?

You can force some transformations to execute explicit pass through code, and I guess that you then could use Netezza specif syntax to transform date to timestamp values. Not so nice from a metadata driven SAS DI perspective, since it will make heavy on specific target DBMS syntax.

The other option is to rely on implicit pass through. In the best of worlds, using formats as mentioned by @Patrick might do the trick. But I haven't tried this scenario, and I suspect that this dosn't either work, or still trigger a data pass via the SAS session.

[Patrick]

@LinusH

When it comes to ETL with all source and target tables in the data base then I've ended up to mostly not use OOTB transformations at all. I found this the only feasible way to ensure that all processing happens within the data base and to allow me to use data base specific syntax.

@EinarRoed

If the OOTB transformations don't let you implement efficient code then consider to create custom transformations for repeated logic using as much of the generated macro variables from this transformation as possible in your code to still end up with something as  metadata driven as possible.