Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
sekharapp
Contributor III
Contributor III

How to get all DATE in one FORMAT in Qlik Sense?

I WANT TO GET ALL DATE IN ONE FORMAT.

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

 

 

Labels (4)
3 Solutions

Accepted Solutions
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

Angela_Zhou
Contributor III
Contributor III

@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

Angela Z.

View solution in original post

Angela_Zhou
Contributor III
Contributor III

@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]

Angela Z.

View solution in original post

8 Replies
Ray_Strother
Support
Support

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
Master II
Master II

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

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Angela_Zhou
Contributor III
Contributor III

@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

Angela Z.
sekharapp
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

@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]

Angela Z.