topic Re: Date/Time format in QlikView

Date/Time format

Mon, 25 Feb 2013 06:02:06 GMT

Hi all,

I'm trying to format a date time, into the format, DD/MM/YYYY, but it keeps returning no values.

I'm using the expressions :

date(date#(CALL_DATETIME,'DD/MM/YYYY hh:mm:ss.[fff] TT'),'DD/MM/YYYY hh:mm:ss') AS [Call Date Time],

date(date#(CALL_DATETIME,'DD/MM/YYYY'),'DD/MM/YYYY') AS [Call Date]

I've attached the sample .qvw.

Any ideas?

Many thanks!

Re: Date/Time format

khadeer — Mon, 25 Feb 2013 06:11:29 GMT

Hi,

I am getting result by using date function. PFA. and check text object. Hope it helps u.

Re: Date/Time format

sushil353 — Mon, 25 Feb 2013 06:32:04 GMT

Hi,

Since your CALL_DATETIME is having value in number, then use date instead of date#.

PFA

Re: Date/Time format

er_mohit — Mon, 25 Feb 2013 06:47:21 GMT

try this

date(date#(CALL_DATETIME,'DD/MM/YYYY hh:mm:ss.[fff] TT'),'DD/MM/YYYY')AS CALL_DATE

HOPE IT HELPS

hi try distinct word after load script.

load distinct

field1,field2,

date(date#(CALL_DATETIME,'DD/MM/YYYY hh:mm:ss.[fff] TT'),'DD/MM/YYYY')AS CALL_DATE

from table ;

hope it helps

Re: Date/Time format

Mon, 25 Feb 2013 10:33:06 GMT

Thanks for your help. - it does work, but returns non-distinct values.

ie. many 24/02/2013, not just a list of 1 distinct date.

Re: Date/Time format

sivarajs — Mon, 25 Feb 2013 11:12:00 GMT

Date# function generally evaluates your expression so you need specify the correct date format which you need to convert.Here the CALL_DATETIME is in integer format not the format you specified in expression.Convert you field to timestamp format

Try this trim(date(date(CALL_DATETIME,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY hh:mm:ss') )

AS [Call Date Time],

trim(date(date(CALL_DATETIME,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )

AS [Call Date]

Re: Date/Time format

Tue, 09 Apr 2013 16:28:48 GMT

Hello Team members,

I have the date field in the '10/20/2013 23:34:00', now I want to convert the above date field into only date format without any time like '10/20/2013'

Re: Date/Time format

Wed, 10 Apr 2013 04:49:37 GMT

Hi Rameshmca,

You could try :

trim(date(date(DATETIME,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )AS [Date],