topic Re: Date Format in App Development

Date Format

shivam_kathairiya — Wed, 26 Jun 2019 12:19:07 GMT

Date format in SQL is DD-MM-YYYY hh:mm:ss tt . while extracting it in qliksense it comes as MMM DD YYYY hh:mmtt/MMM D YYYY hh:mmtt .

Need to convert it in DD-MM-YYYY . Checked with all the function Timestamp#,Date#,Date,Num,Num# but not able its not getting converted.

while checking the len(date field)

MMM DD YYYY hh:mmtt = 19

and for

MMM D YYYY hh:mmtt = 19 (it should be 18) .

Is their is any other way to convert in DD-MM-YYYY.

Re: Date Format

sunny_talwar — Wed, 26 Jun 2019 12:27:12 GMT

Does this not work?

Date(Floor(DateField), 'DD-MM-YYYY') as DateField

If not, then may be this

Date( Floor( Alt( Num(TimeStamp#(DateField, 'MMM DD YYYY hh:mmTT')), Num(TimeStamp#(DateField, 'MMM D YYYY hh:mmTT')) ) ) , 'DD-MM-YYYY') as DateField

Re: Date Format

shivam_kathairiya — Thu, 27 Jun 2019 04:57:52 GMT

No its not working..

i have already try alt also .. and the reason is

when i am writing

Num(TimeStamp#(DateField, 'MMM DD YYYY hh:mmTT'))

and when i am passing

Num(TimeStamp#(DateField, 'MMM  D YYYY hh:mmTT'))

again the Output is same.

Single D format is not getting capture.

Re: Date Format

sunny_talwar — Thu, 27 Jun 2019 11:54:34 GMT

May be try this

Num(TimeStamp#(DateField, 'MMM D YYYY hh:mmTT'))

Re: Date Format

shivam_kathairiya — Thu, 27 Jun 2019 11:59:39 GMT

Its also not working have tried this also . on applying any function num,num3,floor etc null is coming its working for DD format but for D ist coming Null.

Re: Date Format

jonathandienst — Thu, 27 Jun 2019 12:27:32 GMT

Perhaps the SQL output or MMM D YYYY... is padded with a non-printing or white space (other than a space) character, for example a no break space (chr(160) in UTF-8 if I remember correctly). Use PurgeChar() to remove this:

Num( TimeStamp#( PurgeChar(DateField, chr(160)), 'MMM D YYYY hh:mmTT') )

If you can't determine what the character is, then you could also:

Num( TimeStamp#( KeepChar(Upper(DateField), ' :ABCDEFGHIJKLIMNOPQRESTUVXYZ0123456789'), 'MMM D YYYY hh:mmTT') )

You don't really need the full alphabet, so you can trim out the unused letters if you prefer.