Date/Time format

[#1]

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!

     

[khadeer]

Hi,

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

[sushil353]

Hi,

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

PFA

[er_mohit]

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

[#5]

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 · Accepted solution]

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]

[#7]

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'

[#8]

Hi Rameshmca,

You could try :

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