Text to number Date conversion

[Sean_BI]

Dear QV community Team -

I've a date field like below which is in text format.

While reading the file in qv script Month should be converted as below

Month 

Aug'19  

Sep'19

a) Aug

b) I also need month end of each month as 31-Aug-2019, 30-Sep-2019

c) i also need month to be processed as Aug-2019 instead as Aug'19.

I tried to converted the Month like NUM(Date#(Month,'MMM',&'&,'YY')). But it does not work.

Is there any way to convert it.

Rgds,
Sean.

[patilamay]

Maybe try this

For Aug-2019,

Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY')

For Monthend

MonthEnd( Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY') )

Thanks,

[Sean_BI]

thanks a lot.. 

Your text conversion will work, but.

The month value is Aug'19 and not Aug-19. i.e the value has a special character (') highlighted in red.

like i also tried.. Replace(Month, Chr(39),''') as month but this did not work. 

For Aug-2019,

Date(Date#(Date(Date#(Left(Month,3),'MMM'),'MMM')&'-'&Date(Date#(Right(Month,2),'YY'),'YYYY'),'MMM-YYYY'),'MMM-YYYY')

[johnca · Accepted solution]

Try replacing the apostrophe (character 39 or whatever character is there) with a pipe, or your favorite ascii character) first a la;

Date(Date#(Replace(dates,chr(39),'|'),'MMM|YY'),'MMM-YYYY')

Will transform Aug'19 to Aug-2019, etc...

You should be able to get what you need from that.

HTH,

John

[Sean_BI]

Thanks Jonh. It worked.