New to Qlik Sense

[sekharapp]

I WANT TO GET ALL DATE IN ONE FORMAT.

Please see the attached Excel file (DATE FORMAT.xlsx).

[Ray_Strother]

Hello ,

Please review the help link below as you set the format of dates in the load script.

1. Date - script and chart function

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/FormattingFunct...

[edwin]

this will depend on your data.  however, the general solution is to apply the date function with the format you want:

load .... date(INPUTFIELD, 'YOUR DATE FORMAT HERE')

Qlik will attemp to interpret your input data automatically.  however, depending on the data you may need to parse it.  for example if you are loading from different sourcese and the date fields are in different formats, say some of your input date is in MM-DD-YYYY and Qlik cant automatically load it (this is just for illustration as you need to test your data).  you will need to parse it like 

date(subfield(Field,'-',1) & '/' & subfield(Field,'-',2) & '/' & subfield(Field,'-',3), 'MM/DD/YYYY')

or

date(replace(Field,'-','/'), 'MM/DD/YYYY')

assuming your date format is MM/DD/YYYY

you need to take a look at your data and  apply whatever string function you need

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178

https://community.qlik.com/t5/QlikView-Documents/QlikView-Date-fields/ta-p/1484786?attachment-id=120...

[vinieme12 · Accepted solution]

As below

date(alt(floor(fieldname),floor(date#(fieldname,'DD-MM-YYYY')))) as Datefield

side note, check your data because 30-Feb-2015,31-feb-2015 are invalid dates

[Angela_Zhou · Accepted solution]

@sekharapp 

Like the solution from @vinieme12, you can convert any data format by adding floor(date#( into Alt() function as,

date(alt(

   floor(fieldname),

   floor(date#(fieldname,'DD-MM-YYYY')),

   floor(date#(fieldname,'anyInputDateFormat'))

), 'MM/DD/YYYY') as Datefield  //assume MM/DD/YYYY is the final data format you'd like to covert to

[sekharapp]

can you give some examples how does floor function is used for date fields

why i am getting wrong with this expression

date(alt(date#([Claim Registration Date],'DD-MM-YYYY'),date#([Claim Registration Date],'MM/DD/YYYY')),'MM/DD/YYYY')as [Claim Registration Date]

[sekharapp]

can you give some examples how does floor function is used for date fields

why i am getting wrong with this expression

date(alt(date#([Claim Registration Date],'DD-MM-YYYY'),date#([Claim Registration Date],'MM/DD/YYYY')),'MM/DD/YYYY')as [Claim Registration Date]

[Angela_Zhou · Accepted solution]

@sekharapp 
I like to use Floor() is to sync all possible timestamp of one date to single date. if your data do not have such concern, Floor is not necessary.

I think you are missing one possible condition in Alt() to handle input =/= string date, it can be just a date number.

date(alt(
fieldname,   //input as date number, not with any date string format

date#([Claim Registration Date],'DD-MM-YYYY'),

date#([Claim Registration Date],'MM/DD/YYYY')

),'MM/DD/YYYY')as [Claim Registration Date]