topic Re: Date in QlikView

Date

Tue, 03 Sep 2013 06:40:36 GMT

I have in the DB "Datetime" this is "dd.MM.YYYY mm.hh". Like this "12.08.2013 10:45". How can I load just the date "12.08.2013"?

Re: Date

jonathandienst — Tue, 03 Sep 2013 06:42:59 GMT

This should do it:

Date(Floor(Date#(DateTimeField, 'DD.MM.YYYY mm.hh'))) As Date

Hope that helps

Jonathan

Re: Date

dmohanty — Tue, 03 Sep 2013 06:48:53 GMT

Hi,

Can you try this:

= date(datefield,'DD.MM.YYYY')

Re: Date

jonathandienst — Tue, 03 Sep 2013 06:51:53 GMT

Be careful. Date() does not discard the time values, it simply formats the date time field to not display the time part. Use Floor() to truncate the date time field to get rid of the time portion.

Regards

Jonathan

Re: Date

Tue, 03 Sep 2013 07:02:18 GMT

I saw its more than "12.08.2013 10:45". It's "12.08.2013 10:45:10.000000000"

Re: Date

Tue, 03 Sep 2013 07:17:31 GMT

like:

Select id,

name,

=date(datefield,'DD.MM.YYYY')

FROM table;

or how do I use the "="?

Re: Date

er_mohit — Tue, 03 Sep 2013 07:44:40 GMT

tRY THIS IN TEXT OBJECT

=SubField('12.08.2013 10:45.10000000000000',' ',1)

You 'll got the Date

or in script you can do this way

load

id,

name,

subfield(datefield,' ',1) as Date

FROM table;

Re: Date

Tue, 03 Sep 2013 08:10:22 GMT

I tried your expressions but i didnt get it working

Re: Date

tresB — Tue, 03 Sep 2013 08:16:40 GMT

Have you tried using floor() function, as suggested by Jonathan Dienst ? That should work fine. If all above not working, please share a sample file.

Re: Date

Tue, 03 Sep 2013 08:28:16 GMT

My Code:

ODBC CONNECT TO [xxx](xxx,xxx)

SQL SELECT Id,

action,

DATETIME //Sample: 12.08.2013 14:30:30.000000000

Date(Floor(Date#(DATETIME, 'DD.MM.YYYY mm.hh'))) As Date

FROM XX."XX";

Then I got an error.

Script Error.... Ausdruck fehlt

Scripr Error.... Expression wrong

Re: Date

tresB — Tue, 03 Sep 2013 08:30:37 GMT

put a comma after DATETIME. And yes, you are trying to use qv function under SQL. that won't work.

try like:

LoadedTable:

Load *,

Date(Floor(Date#(DATETIME, 'DD.MM.YYYY mm.hh'))) As Date ;

ODBC CONNECT TO [xxx](xxx,xxx)

SQL SELECT Id,

action,

DATETIME

FROM XX."XX";

Re: Date

Tue, 03 Sep 2013 08:36:15 GMT

Now i can load it, but the listbox Date is empty

how do i have to set the "DD.MM.YYYY mm.hh" if it is 12.08.2013 10:12:20.000000000

Re: Date

tresB — Tue, 03 Sep 2013 08:39:44 GMT

This?

LoadedTable:

Load *,

Date(Floor(Date#(DATETIME, 'DD.MM.YYYY mm.hh.fff'))) As Date ;

ODBC CONNECT TO [xxx](xxx,xxx)

SQL SELECT Id,

action,

DATETIME

FROM XX."XX";

And if this too not working, try simply:

Date(Floor(DATETIME)) As Date

Re: Date

Tue, 03 Sep 2013 08:50:30 GMT

Now it works. With just Date(Floor(DATETIME)) as Date

Thank you verry much

Re: Date

Tue, 03 Sep 2013 08:55:32 GMT

Now I have a Date

Can I load the Data with Where Date >= '19.08.2013'?