topic Re: Change from DATETIME to DATE in QlikView

Change from DATETIME to DATE

Fri, 26 Feb 2016 18:27:29 GMT

I am pulling date directly from my database and it looks like this : 2005-02-28 15:46:54

I want to seperate out the date part from the time.

when I am pulling from QVD, DATE(FLOOR(CREATED_DT)) works.

but since it is pulling directly from database, it says the date function is not built in thus I cannot use it.

how can I take out only the date part when i am pulling directly from database?

Re: Change from DATETIME to DATE

sunny_talwar — Fri, 26 Feb 2016 18:43:08 GMT

May be this:

LOAD Date(Floor(CREATED_TimeStamp)) as CREATED_DT,

Time(Frac(CREATED_TimeStamp)) as CREATED_Time;

LOAD TimeStamp(TimeStamp#(CREATED_DT, 'YYYY-MM-DD hh:mm:ss') as CREATED_TimeStamp

FROM Source;

Re: Change from DATETIME to DATE

maxgro — Fri, 26 Feb 2016 18:51:50 GMT

you can use the interpretation function (# at the end) and formatting function to get your result

also frac and floor can be useful to get the time and the date part

Re: Change from DATETIME to DATE

Fri, 26 Feb 2016 19:16:45 GMT

Hi Sunny,

Thank you for your answer.

but since I am pulling directly from database without QVD

LOAD does not work but only SELECT works.

and when I use SELECT, I cannot seem to use any functions.

is that true?

do you know anyway I can trim the date part when I am using SELECT?

Re: Change from DATETIME to DATE

Fri, 26 Feb 2016 19:17:08 GMT

Hi maxgro,

Thank you for your answer.

but since I am pulling directly from database without QVD

LOAD does not work but only SELECT works.

and when I use SELECT, I cannot seem to use any functions.

is that true?

do you know anyway I can trim the date part when I am using SELECT?

Re: Change from DATETIME to DATE

sunny_talwar — Fri, 26 Feb 2016 19:34:36 GMT

You need to use a preceding load as I mentioned above:

LOAD *,

Date(Floor(CREATED_TimeStamp)) as CREATED_DT,

Time(Frac(CREATED_TimeStamp)) as CREATED_Time;

LOAD *,

TimeStamp(TimeStamp#(CREATED_DT, 'YYYY-MM-DD hh:mm:ss') as CREATED_TimeStamp;

SQL SELECT .....

Put the red part on top of your SQL Select statement

Re: Change from DATETIME to DATE

Digvijay_Singh — Fri, 26 Feb 2016 19:44:09 GMT

You don't need to trim it in first go when reading through SELECT, Qlikview provides preceding load (As Sunny mentioned two extra load on top of select) or RESIDENT load to format your date using interpretation functions suggested by MaxGro.

May be I misunderstood the requirement.

Re: Change from DATETIME to DATE

maxgro — Fri, 26 Feb 2016 19:52:44 GMT

load works in preceding load

Preceding Load‌

you should put the interpretation and formatting function in the load part