Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
I have an issue I need to load data from a specific time (from 04:00:00 to 15:00:00).
In my load script in my call table i used the below:
Load
Time(subfield(CallUniversalTime, ' ' ,'hh:mm:ss')) as Time,
SQL SELECT *
FROM "HN_Ondata".dbo.ODCalls where FirstCampaign = '1743'and Time > '04:00:00' and Time < '15:00:00';
However, it does not work.
Any idea?
Thanks for your help.
Kind Regards,
Hasvine
Hi,
Try by converting the time to Number format while equating in the Time field.
Hi Vivek,
Could you give me an example please?
KR,
Hasvine
First you need a semicolon before the SQL command:
Load *,
Time(subfield(CallUniversalTime, ' ' ,'hh:mm:ss')) as Time
;
SQL SELECT *
FROM "HN_Ondata".dbo.ODCalls where FirstCampaign = '1743'and Time > '04:00:00' and Time < '15:00:00';
Secondly - test your SQL query in SQL Management Studio (or other tool like Toad). Verify the data type of [Time]. SQL does not have a time data type, so this will be a datetime field. What is the date portion of the field? Perhaps that is why the query is failing.
Oh, and if Time is indeed a date time field, your load expression should be
Load *,
Time(CallUniversalTime, 'hh:mm:ss')) as Time;
or, to remove the date portion:
Load *,
Time(Frac(CallUniversalTime), 'hh:mm:ss')) as Time;
Thank you for your help.
I finally set the filter in the set it in the expression.
KR,
Hasvine