Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

Variable time field length

Hello,

I have a problem with filtering certain time field in script load. I want to set it automatic.

My script is: (date format is 'MM. DD. YYYY h:mm:ss')

LET transaction_from = '10. 1. 2017 0:00:00';

LET transaction_to = '10. 31. 2017 23:59:59';

LET foundation_from = '11. 1. 2017 0:00:00';

[Transaction]:

Load

  [t_rbid]                     as [ID object],

  [t_leac]                     as [ID account],

  [t_trdt]                      as [Date transaction],

  [t_crdt]                      as [Date foundation],

  [t_crus]                      as [ID employee],

  [t_amnt]                     as  [Amount]

  ;

SQL SELECT

  *

FROM [dbo].[ttfgld482]

[t_trdt] >= '$(transaction_from)' and [t_trdt] <= '$(transaction_to)' and [t_crdt] >= '$(foundation_from)'

;

I want to rewrite date by some function in order to it goes automatic. I try to use function monthstart and monthend but it doesn´t work me.

Could you help me ?

Thank you

1 Solution

Accepted Solutions
jozisvk11
Creator
Creator
Author

The solution is:

LET transaction_from = Date(MonthStart(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET transaction_to = Date(MonthEnd(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET foundation_from = Date(MonthStart(Today()), 'MM. DD. YYYY h:mm:ss');

View solution in original post

1 Reply
jozisvk11
Creator
Creator
Author

The solution is:

LET transaction_from = Date(MonthStart(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET transaction_to = Date(MonthEnd(Today(),-1),'MM. DD. YYYY h:mm:ss');

LET foundation_from = Date(MonthStart(Today()), 'MM. DD. YYYY h:mm:ss');