Custom format in QlikView for DateTime

[#1]

Hi All,

I need to get DateTime difference and we have a function for that as below,

=Interval(Enddate-Startdate, 'mm')

But for this we need date in specific format i.e. as below,

let Startdate = num(timestamp#('2012/10/02  11:55:25 PM','YYYY/MM/DD  hh:mm:ss TT'));

let Enddate= num(timestamp#('2012/10/03  01:55:26 AM','YYYY/MM/DD  hh:mm:ss TT'));

I am getting data from excel where my date format is as below,

8/1/2013  3:54:34 AM

and unable to make it in above format even after using custom format.

Can any one please guide me how can I do custom format in QlikView so that I will get date in below format 

'2012/10/02  11:55:25 PM'

Thanks,

Abhi

[kaushiknsolanki]

Hi,

    I guess the Date which you are getting from Excel is already in Date format, so you just need to use below function.

    Try this.

    Load Date(StartDate,'YYYY/MM/DD  hh:mm:ss TT') as Start_Date

     From XYZ.xls;

Regards,

Kaushik Solanki

[jagan]

Hi,

Please check format code for dates

Dates

•   To describe the day, use the symbol "D" for each digit.

• To describe the month number, use the symbol "M" or "MM" for one or two digits. "MMM" denotes short month name in letters as defined by the operating system or by the override system variable MonthNames in the script. "MMMM" denotes long month name in letters as defined by the operating system or by the override system variable LongMonthNames in the script.

• To describe the year, use the symbol "Y" for each digit.

• To describe the weekday, use the symbol "W". One W will return the number of the day (e.g. 0 for Monday) as a single digit. "WW" will return the number with two digits (e.g. 02 for Wednesday). "WWW" will show the short version of the weekday name (e.g. Mon) as defined by the operating system or by the override system variable DayName in the script. "WWWW" will show the long version of the weekday name (e.g. Monday) as defined by the operating system or by the override system variable LongDayName in the script

• Arbitrary separators can be used.

Times

•   To describe the hours, use the symbol "h" for each digit.

• To describe the minutes, use the symbol "m" for each digit.

• To describe the seconds, use the symbol "s" for each digit.

• To describe the fractions of a second, use the symbol "f" for each digit.

• To describe the time in AM/PM format, use the symbol "tt" after the time.

• Arbitrary separators can be used.

Examples (Date):

YY-MM-DD describes the date as 01-03-31.

YYYY-MM-DD describes the date as 2001-03-31.

YYYY-MMM-DD describes the date as 2001-Mar-31.

31 MMMM YYYY describes the date as 31 March 2001.

M/D/YY describes the date as 3/31/01.

W YY-MM-DD describes the date as 6 01-03-31.

WWW YY-MM-DD describes the date as Sat 01-03-31.

WWWW YY-MM-DD describes the date as Saturday 01-03-31.

Examples (Time):

hh:mm describes the time as 18:30

hh.mm.ss.ff describes the time as 18.30.00.00

hh:mm:tt describes the time as 06:30:pm

Examples (Timestamps):

YY-MM-DD hh:mm describes the timestamp as 97-03-31 18:30

M/D/Y hh.mm.ss.ffff describes the timestamp as 3/31/97 18.30.00.0000

Note : From Qlikview Help file.

Regards,

Jagan.