Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]
Hi,
I am getting result by using date function. PFA. and check text object. Hope it helps u.
Hi,
Since your CALL_DATETIME is having value in number, then use date instead of date#.
PFA
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
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.
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]
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'
Hi Rameshmca,
You could try :
trim(date(date(DATETIME,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )AS [Date],