topic Re: DATE FORMAT in QlikView

DATE FORMAT

upaliwije — Fri, 19 Feb 2016 08:10:00 GMT

In my data table date is recorded in the following way

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

I want to return the date without time as given below

17-May-2007

Pls help me to format this in my load script

Re: DATE FORMAT

Gysbert_Wassenaar — Fri, 19 Feb 2016 08:14:20 GMT

It depends on what's really in your database, string values or timestamp/datetime values.

In the first case you need to use something like Date(Floor(Timestamp#(MyDate,'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY'). In the second case you can use Date(Floor(MyDate),'DD-MMM-YYYY')

Re: DATE FORMAT

jonathandienst — Fri, 19 Feb 2016 08:15:05 GMT

This is how to do this with date functions:

Date(Floor(Date#(DateField, 'dd-MMM-yyyy hh:mm:ss')), 'dd-MMM-yyyy') As DateField

The Floor() is essential to produce a date, rather than a datetime value. The formatting function, Date() does NOT remove the time component.

Re: DATE FORMAT

jagan — Fri, 19 Feb 2016 08:32:50 GMT

HI,

Try like this

LOAD

Date(Floor(Date#(DateField,'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY') AS Date_New

FROM DataSource;

Regards,

Jagan.

Re: DATE FORMAT

upaliwije — Fri, 19 Feb 2016 08:44:38 GMT

I tried with all you ways. But I do not get the desired output

When I load the date with following command

LOAD *

PERIOD_TO

....

Output I get is 5/1/2013 12:00:00 AM

but when I load either of the following command no result is shown

DATE(FLOOR(Date#(POL_PERIOD_TO,'DD-MMM-YYYY')),'DD-MMM-YYYY') AS PERIOD_TO,

Date(Floor(Timestamp#(POL_PERIOD_TO,'DD-MMM-YYYY hh:mm:')),'DD-MMM-YYYY')AS PERIOD_TO,

Pls help

Re: DATE FORMAT

PrashantSangle — Fri, 19 Feb 2016 08:48:41 GMT

Hi,

one more way

use subfield(datefield,' ',1)

date(Floor(Timestamp#(datefield,'DD-M-YYYY hh:mm:ss')),'DD-MMM-YYYY')

Edit :

date(Floor(Timestamp#(datefield,'DD-M-YYYY hh:mm:ss TT')),'DD-MMM-YYYY')

Note : adjust your format as per data you have.

Regards

Re: DATE FORMAT

jonathandienst — Fri, 19 Feb 2016 08:51:02 GMT

That;s not what Gysbert suggested. You need to include the time formatting in the Date# function

Date(Floor(Date#(POL_PERIOD_TO, 'DD-MMM-YYYY hh:mm:ss')),'DD-MMM-YYYY') AS PERIOD_TO,

If that does not work, I suggest that you upload some sample dates.

Re: DATE FORMAT

upaliwije — Fri, 19 Feb 2016 09:10:13 GMT

I have attached my QV pls advise

Re: DATE FORMAT

Fri, 19 Feb 2016 09:21:08 GMT

Hi Upali,

Instead of doing with date conversion, can you please try using below by using Index and left functions.

input:

load * inline [

date

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

];

output:

load left(date,Index(date,' ')-1) as oo

Resident input;

Thanks,

Sreeman

Re: DATE FORMAT

senpradip007 — Fri, 19 Feb 2016 09:28:33 GMT

Try like

LOAD *, date(date#(date_time, 'DD-MMM-YYYY hh:mm:ss'), 'DD-MMM-YYYY') as Date Inline [

date_time

17-May-2007 19:05:11

17-May-2007 19:07:36

17-May-2007 19:08:58

17-May-2007 19:09:38

17-May-2007 19:13:41

17-May-2007 19:23:46

];

Re: DATE FORMAT

tresB — Fri, 19 Feb 2016 09:30:29 GMT

Your date field seems to be coming as proper date. So, you don't have to use date#(), try simply:

Date(Floor(YourDate), 'DD-MMM-YYYY') as NewDate

Re: DATE FORMAT

upaliwije — Fri, 19 Feb 2016 09:39:49 GMT

Thanks all for your contribution