How to calculate full Date from Year.Month String

[SAPDataMigrate]

Hi Experts,

I need help in building a date out of string field.

I have a varchar field of CALMONTH which comprises of values like '201604', '201512' i.e. combination of year and month.

I need to create a new CALDATE column in query transform(date datatype), whose mapping should contain the logic to get a date from CALMONTH field. CALDATE should contain value like '2016.04.01', '2015.12.01'  (YYYY.MM.DD)

I need to make day as '01' always.

Please give me logic to handle this. Thanks in advance.

[former_member187605 · Accepted solution]

to_date(CALMONTH||'01','YYYYMMDD')

[SAPDataMigrate · Accepted solution]

Hi Dirk,

Thanks for quick response. I really needed that!

However this solution is returning only null values. I did this mapping :

to_date(CALMONTH||'01','YYYYMMDD')

Also, as I want the data in YYYY.MM.DD format, I was trying this :

to_date(\'[substr(CALMONTH, 1, 4)||\'.\'||substr(CALMONTH, 5, 2)||\'.\'||\'01\']\', 'YYYY.MM.DD')

It is having some error and hence not able to map. Can you please rectify?

Thanks

[former_member187605 · Accepted solution]

If CALMONTH is a varchar, as you said, CALMONTH||'01' will contain '20160401', '20151201'... in the format 'YYYYMMDD', so to_date as specified will generate the correct date.

Dates do not have an external format! They are stored in an internal binary format, that wil vary from application to application (just like any non-character data, numbers for instance).

You can only specify a date format when you (explicitly or implicitly) convert them back to a string. So what you are trying to do is complete nonsense. I am sorry.

[SAPDataMigrate · Accepted solution]

Hi Dirk,

Thanks for being critical about this. What I was trying to do was sensible but approach was not correct. I thought to just extract the year string and month string from CALMONTH and then concatenate with '01'. I was interpreting the to_date function in incorrect manner.

The thing which actually worked out for me was :

to_date(replace_substr(CALMONTH,'.000', '')||'01', 'YYYYMMDD')

It automatically returns the date in this format -> 2015.12.01

Actually I got confused because of no date separator in YYYYMMDD format and I was expecting separator as '.' ALthough, later realized that it is implicit. Can we change the separator as '-' or '/'

Thanks!

[former_member187605 · Accepted solution]

No worries, so many people get confused about this. If you search this forum, you'll find many posts around this subject. In fact, there was another one, just earlier today .

Looking at your solution, it is cleaer that CALMONTH doesn't contain values like '201604', '201512'..., but rather '201604.000, '201512.000'... Obviously, that makes a difference.