Monthname and monthnumber to date conversion

[sagarpawar]

HI,

Can you please help me on below scenario.

In one table I have month name as 'January', 'February',...  and in another table we have month number as 1,2,3....

I want to convert both into date (1st date of the month) as 01/01/Year,01/02/Year,01/03/Year...

How we will achieve that one?

It will really helpful.

Note='We have to fetch year from current date of calendar i.e. 2019'

Thanks,

Sagar

[volakakis]

Hi Sagar

date('01/' & if(MONTH = 'January',1,MONTH)& '/2019','DD/MM/YYYY') as NewField

Just expand the if statement to all months

Nikos

[sunny_talwar · Accepted solution]

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

[Vegar · Accepted solution]

---

@sunny_talwar wrote:

May be try this

The table with Month Names (January, February,... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as Date

The table with Month Numbers (1, 2, 3... etc)

Date(MakeDate(YearField, Month(Date#(MonthName, 'M')), 1)) as Date

---

To use @sunny_talwar suggestion date#(MonthName ,'MMMM') you will need to make sure that your LongMonthNames-variable is defined correctly.

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';