how do I handle if field contains some thing else For example, number or any letter.
so in the ('2015-01-20L20:23:56X','L',1) how can we make it dynamic to include changes
in the same position where 'L' is.
I would just pick the first 10 characters using Left if the format is going to be YYYY-MM-DD (and not YYYY-M-D)
Date(Date#(Left('2015-01-20L20:23:56X', 10), 'YYYY-MM-DD'), 'M/D/YYYY')
there are two basic approaches to interprete a timestamp or part of it as date:
Either use a single interpretation function with an appropriate format code that digest the complete timestamp string and returns a QV dual value (edit: then use numeric & QV Date functions to retrieve the date part), or use string functions like LEFT(), SUBFIELD(), etc. to chop the timestamp string into parts and pipe the parts into interpretation functions.
Latter is what Gysbert and I did, using Left() / Subfield(). Which one is better? This depends on your input string format and possible changes to the format across your records.
If the delimiter between date and time changes, use Left() as I did above, If it doesn't change or the date part changes in number of character, a Subfield() might be better.
Note that I haven't used an explicite interpretation function like Date#(), because I bet on the implicite date interpretation of a ISO date format.
Hope this helps,
Option 1: date(LEFT(NUM(DATE#('2015-01-20 20:23:56','YYYY-MM-DD hh:mm:ss')),5),'MM/DD/YYYY')
Option2: If MM DD is always 2 chars, then use this DATE(LEFT('2015-01-20 20:23:56',10),'MM/DD/YYYY')