Time range

[#1]

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

[vivek_niti]

Hi,

Try by converting the time to Number format while equating in the Time field.

[#3]

Hi Vivek,

Could you give me an example please?

KR,

Hasvine

[jonathandienst]

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.

[jonathandienst]

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;

[#6]

Thank you for your help.

I finally set the filter in the set it in the expression.

KR,

Hasvine