Date Format

[shivam_kathairiya]

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.

[sunny_talwar]

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

[shivam_kathairiya]

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.

[sunny_talwar]

May be try this

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

[shivam_kathairiya]

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.

[jonathandienst]

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.