Kdb SQL (ODBC connexion)

Community Support
1

Hi Guys,

I have an sql query on KDB as follows:

sql: "SELECT CUSIP, EXEC_PRC, TICK_TMSTMP , TICK_EFF_DT FROM TABLE WHERE ";

sql,:“TICK_EFF_DT = TO_DATE('”, (string x),“‘,‘YYYY.MM.DD’)  AND CUSIP IN (’”, (string z),"')  ";

The parameters z and x in the sql query are cusip and dates resp ( it is a dynamic sql query). 

I would like to improve this query by adding another timestamp filter (highlighted in bold)

my timestamp in the database has the following format TICK_TMSTMP=(‘2016-04-04 06:00:00’).

How can I improve the sql query but adding a timstamp filter please? I would like to keep the same dynamic query but add another dynamic filter that focuses on the timestamp

Many Thanks

S

2

Hi Saad,

If all you need is to format an input to the corresponding SQL filter, then the following should work.

q)format:{@[x 0;4 7;:;“-”]," “,”:“sv “0”^-2$x[1 2 3]}stringdatehhmmss$
/or more simply..
q)format:{{@[x[0];4 7;:;”-“],” “,-4_x[1]}stringdatetime$x}
q)format .z.p
“2016-06-07 15:06:13”
q)sql,:“AND TICK_TMSTMP=('”,format[.z.p],”') "    / replace .z.p with the parameter your function is expecting

Hope this helps.

Thanks,

Connor

3

q){" "0:“dv”$:1#x}.z.p
“2016-06-15 14:37:43”