Problem with date in loading script

[ecabanas]

Hi,

I did this script to load data from MySQL and I have a problem. The table has records from part of 2015 and 2016, but I only want to load the 2016 records.

The field created_at is a timestamp and did not do the filter that I desire:

SalesLine:

LOAD created_at as CreatedatSalesLine,

order_id,

'PV' & right('00000000'&order_id,8) as SalesId,

if(IsNull(product_id),code,product_id)as ItemId,

//code as ItemId,

price,

gift,

quantity_in_order,

quantity_purchased,

quantity_delivered,

1+(ApplyMap('Tax_Item_Map',product_id)/100)as Tax,

(price/1+(ApplyMap('Tax_Item_Map',product_id)/100))as Import_Brut,

ApplyMap('Date_Invoice_Map',order_id)as Date_Invoice;

SQL SELECT created_at ,order_id, product_id ,price, gift,quantity_in_order,quantity_purchased,quantity_delivered,code FROM ulaoffice.NS_order_lines

where floor(created_at)>42369;

Many thank's for your help

Eduard

[tamilarasu]

I agree that it wil not work same in Qlikview as like Mysql.

[Peter_Cammaert]

Well the floor() function will operate the same in SQL as in QV. But an RDBMS will most probably store a Timestamp differently from QlikView, QlikView uses Excel binary storage in which the integer part represents the number of days since xx/12/1899 and the fraction represents the time as part of a day (0.5 meaning 12:00)

But does an (every) RDBMS store DATETIME values in the same way? And how about DATE values?

[tamilarasu]

Thanks for the explantion. No. DATE and DATETIME storing procedure (format) differs based on the database. (In mysql and MSSql, default format is YYYY-MM-DD whereas DD-MON-YY in Oracle).