Change from DATETIME to DATE

[#1]

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?

[sunny_talwar]

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;

[maxgro]

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

[#4]

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?

[#5]

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?

[sunny_talwar]

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

[Digvijay_Singh]

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.

[maxgro]

load works in preceding load

Preceding Load‌

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