Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date/Time format

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!

     

1 Solution

Accepted Solutions
sivarajs
Specialist II
Specialist II

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]

View solution in original post

7 Replies
khadeer
Specialist
Specialist

Hi,

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

sushil353
Master II
Master II

Hi,

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

PFA

er_mohit
Master II
Master II

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.

like

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


Not applicable
Author

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.

sivarajs
Specialist II
Specialist II

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]

Not applicable
Author

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'

Not applicable
Author

Hi Rameshmca,

You could try :

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