Forum Discussion

Eric's avatar
Eric
Advocate I
6 years ago
Solved

Convert YYYYMMDD String to DateTime w/o breaking query folding

The date field in my source file is stored as a string in YYYYMMDD format.  I need to convert it to a DateTime field in order to do incremental refresh and I need to do that without breaking query fo...
Need Help
  • Eric's avatar
    Eric
    6 years ago

    Thank you for the reply.  Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn.  Both break query folding because I'm changing a text field to a date field.  Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step.  It's not query folding but it may well improve performance, we'll see.

Eric's avatar
Eric
Advocate I
6 years ago

Thank you for the reply.  Yes, I have already tried the Table.TransformColumnTypes approach along with Table.AddColumn.  Both break query folding because I'm changing a text field to a date field.  Right now, I've switched back to odbc.query and I am pushing as much into the SELECT query as possible to leverage the service on the initial step.  It's not query folding but it may well improve performance, we'll see.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I'm also looking for a solution. All the dates in my SQL database are stored as text, not as numbers.
And I can't convert them to date without breaking the query folding (which means, for example, that I can't use those fields for incremental refresh).

 

I wonder if there is a way to convert without breaking query folding. There should be a way for PQ to convert in natural SQL language, since there is a CAST() function in SQL, no?