DD/MM/YYYY hh:mm to DD/MM/YYYY

[jlampard40]

Hi - I have an Excel load which has been extracted from a number of different systems within our organisation.  Unfortunately some of the dates extracted use the format DD/MM/YYYY hh:mm and we need to ensure Qlikview just sees them as DD/MM/YYYY only i.e. without the time part!  There are too many entries to do this within the Excel sheets themselves and this is data which is pulled on a daily basis so way too time consuming!

i need Qlikview to convert it for me, either upon load or (preferably) using an expression within Qlikview to add myself to a table which then displays the data in the correct format.

Your help is appreciated!  Many thanks.

[DavidM]

You can use the floor & date function for this:

date(floor(date),'DD/MM/YYYY') as date

If you don't floor your field you might encounter troubles where the same day may be shown twice, as Qlik converts the date into number, so for HH:MM you would get decimal numbers.

[jlampard40]

Where do I put this?  Into script or as expression?  New to this so learning all the time - many thanks.

[DavidM]

This goes in the load script. The date in brackets should be the name of your field (column) in Excel file. Date will be actual name of the field in Qlik

date(floor(date),'DD/MM/YYYY') as date

[jlampard40]

Thank you.  Still not sure where exactly to put it in script though.  Do you have an example screen shot you could show me to advise location within script.  Many thanks

[DavidM]

in the script where are you actually loading your file you have something like:

Load

columnname1,

columnname2,

...

From path to your excel file

Find your date there and replace it with the formula I provided you

[jlampard40]

Hi - I tried that.  As follows:

date(floor([AD_Event_DT]),'DD/MM/YYYY') as EventDate

My date field is [AD_Event_DT].  Qlikview doesn't appear to recognise it and so this field just has a '-' where dates should be.  Any thoughts?

[DavidM]

Most likely Qlikview doesn't recognize the field as timestamp. Try this:

date(floor(timestamp([AD_Event_DT])),'DD/MM/YYYY') as EventDate

[Brett_Bleess]

Check the following Design Blog link as well, it may help you better understand things:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

Here are a couple of Help links as well:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/date-...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Forma...

Regards,
Brett