topic Re: Date Issue!!!!! in QlikView

Date Issue!!!!!

Tue, 29 Apr 2014 08:36:09 GMT

Hi,

I have a date in my database the format is

date
8/1/2013 01:00:58 am
8/1/2013 01:13:58 am
8/1/2013 01:20:58 pm

i want convert these date in to 24 hrs when am fulling the into Qlikview can any one help me

how to wright a Time stamp for date column

Re: Date Issue!!!!!

MK_QSL — Tue, 29 Apr 2014 08:38:20 GMT

Load this in your script..

Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss tt')) as date

You will get result as below

08/01/2013 01:00:58

08/01/2013 01:13:58

08/01/2013 13:20:58

Here I have considered that your format is d/m/yyyy. But if it is m/d/yyyy, you can change accordingly

To get simply date, you can use below

Date(FLOOR(Timestamp#(date,'d/m/yyyy hh:mm:ss tt'))) as date

To get time

Time(Frac(Timestamp#(date,'d/m/yyyy hh:mm:ss tt'))) as time

Re: Date Issue!!!!!

CELAMBARASAN — Tue, 29 Apr 2014 09:08:32 GMT

You could use date format as

D/M/YYYY HH:mm:ss

H represent 24 hr format, h represents 12 hr format

Don't use M in time because it will represent month and m is for minutes.

Hope it helps

Re: Date Issue!!!!!

Fri, 02 May 2014 07:01:24 GMT

I have Date field With above format in that some FIELDS are having (AM,PM) & some fields are having 24 hrs format finally i want a data in AM PM format

can u suggest plsssssss

Re: Date Issue!!!!!

nizamsha — Fri, 02 May 2014 07:03:14 GMT

TableA:

LOAD * Inline [

Date

1-04-2014 15:15

2-05-2014 11:20

];

LOAD Date( Date#(Date,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm TT') as N1 Resident TableA;

Re: Date Issue!!!!!

Fri, 02 May 2014 07:07:44 GMT

hi try this,

Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss:tt'))) as date

Re: Date Issue!!!!!

anbu1984 — Fri, 02 May 2014 07:12:17 GMT

Load Alt(Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss tt')),Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss'),'d/m/yyyy hh:mm:ss tt'));

Load * Inline [

date

08/01/2013 01:00:58 PM

08/01/2013 01:13:58 AM

08/01/2013 13:20:58 ];

Re: Date Issue!!!!!

MK_QSL — Fri, 02 May 2014 11:22:29 GMT

Load

Alt(

TimeStamp(TimeStamp#(Date,'D/M/YYYY hh:mm:ss tt'),'D/M/YYYY hh:mm:ss TT'),

TimeStamp(TimeStamp#(Date,'D/M/YYYY hh:mm:ss'),'D/M/YYYY hh:mm:ss TT'),

) as Date

Inline

[

Date

8/1/2013 01:00:58 am

8/1/2013 01:13:58 am

8/1/2013 01:20:58 pm

9/1/2013 13:10:10

10/1/2014 15:10:10

];

Update : Ohhh... Anbu has already replied similar to my one

Re: Date Issue!!!!!

Anonymous — Fri, 02 May 2014 11:42:27 GMT

Hi,

Use HH:mm:ss as format

HTH

Regards,

NRC.

Re: Date Issue!!!!!

Fri, 02 May 2014 11:51:28 GMT

i want to distribute the date and time separate

Re: Date Issue!!!!!

CELAMBARASAN — Fri, 02 May 2014 11:55:16 GMT

Try this

Date(Floor(DateField)) AS Date,

Time(Frac(DateField)) AS Time

Re: Date Issue!!!!!

MK_QSL — Fri, 02 May 2014 11:58:06 GMT

Load

Alt(

TimeStamp(TimeStamp#(Date,'D/M/YYYY hh:mm:ss tt'),'D/M/YYYY hh:mm:ss TT'),

TimeStamp(TimeStamp#(Date,'D/M/YYYY hh:mm:ss'),'D/M/YYYY hh:mm:ss TT'),

) as TimeStampDate,

Alt(

Date(Floor(TimeStamp#(Date,'D/M/YYYY hh:mm:ss tt'))),

Date(Floor(TimeStamp#(Date,'D/M/YYYY hh:mm:ss'))),

) as Date,

Alt(

TIME(FRAC(TimeStamp#(Date,'D/M/YYYY hh:mm:ss tt'))),

Time(FRAC(TimeStamp#(Date,'D/M/YYYY hh:mm:ss'))),

) as Time

Inline

[

Date

8/1/2013 01:00:58 am

8/1/2013 01:13:58 am

8/1/2013 01:20:58 pm

9/1/2013 13:10:10

10/1/2014 15:10:10

];

Re: Date Issue!!!!!

anbu1984 — Fri, 02 May 2014 11:59:05 GMT

Load Date(Alt(Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss tt')),Timestamp(Timestamp#(date,'d/m/yyyy hh:mm:ss'),'d/m/yyyy hh:mm:ss tt')),'d/m/yyyy'),

Alt(Time(Timestamp#(date,'d/m/yyyy hh:mm:ss tt')),Time(Timestamp#(date,'d/m/yyyy hh:mm:ss'),'hh:mm:ss tt'));

Load * Inline [

date

08/01/2013 01:00:58 PM

08/01/2013 01:13:58 AM

08/01/2013 13:20:58 ];