Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rohanwilliams
New Contributor II

Convert 'DD/MM/YYYY hh:mm AM' into date

please help me to convert 13/10/2017 09:10 AM into date ( day,'DD/MM/YYYY hh:mm')

- not working =date(date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')

1 Solution

Accepted Solutions

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

Try this

=Date(Date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm')

8 Replies

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

That was typo you have to use Timestamp rather than date because you have Time in your date format then try with timestamp

Try with this and check

=Timestamp(Timestamp#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm')

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

Try this

=Date(Date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm')

MVP
MVP

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

And if you want to convert the timestamp into a date, don't forget to stripp of the time part:

=Date(Floor(Date#('13/10/2017 09:10 AM' ,'DD/MM/YYYY hh:mm TT')),'DD/MM/YYYY')


edit: added missing single quote and moved parenthese

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

Missed a single quote at the end of the format

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

Another way is

Load

*,

Timestamp(Timestamp#( Left(DateFormat,16),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewFormat2,

Timestamp(Timestamp#(DateFormat ,'DD/MM/YYYY hh:mm TT'),'DD/MM/YYYY hh:mm') as NewFormat;

Load * Inline

[

DateFormat

"13/10/2017 09:10 AM"

];

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

This is risky....

Timestamp(Timestamp#( Left(DateFormat,16),'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as NewFormat2,


Because if you do this, then how will you differentiate between morning 8 or evening 8?

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

May be with AM and PM though then in final format AM / PM should be there.

Re: Convert 'DD/MM/YYYY hh:mm AM' into date

Yes, the second one is fine, but just wanted to point out that the one where you use left() function will always think that the date is in AM.