How load Datetime Column from SQL?

[malome64]

SQL column Datum is DateTime Type, but Time part is 00:00:000.

I tested 3 metods conversion column Datum.

In QVD, DatumSQL convert to Date (32bit integer), another two to 32bit Integer + String. I need show DatumStr format on sheets.

But what about size QVD and Set Analysis?

Which date format I should use in LOAD Script?

[daveamz · Accepted solution]

Hi Marian,

It is better to use the preceding load and format the date using Qlik's Date() function or Timestamp() if you want to keep also the time. Or, as the best practice dictates, it is better to split the date and the time in separate fields:

Test:

LOAD

     Date(Floor(Datum), 'DD.MM.YYYY') AS Date, //Floor will keep only the date from a timestamp

     Time(Datum, 'hh:mm:ss')                     AS Time;

SQL SELECT

      Datum

FROM dbo.table;

Regards,

David

[marcus_sommer]

I suggest just to load Datum from the database and convert it properly (pure numeric will be sufficient) within a Preceding Load and connect this field to a master-calendar: How to use - Master-Calendar and Date-Values‌ which could contain all your needed period-fields in all formattings.

- Marcus

[Vamsi0529]

Is it possible doing the transformation in SQL level instead of doing transformations in qlik

Eg:

Test:

LOAD

     Datum;

SQL SELECT

      Datum ( I want the date transformation here only, Need format YYYY/MM/DD )

FROM dbo.table;