Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
@sunny_talwar wrote:May be try this
The table with Month Names (January, February,... etc)
Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as DateThe 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';
Hi Sagar
date('01/' & if(MONTH = 'January',1,MONTH)& '/2019','DD/MM/YYYY') as NewField
Just expand the if statement to all months
Nikos
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
@sunny_talwar wrote:May be try this
The table with Month Names (January, February,... etc)
Date(MakeDate(YearField, Month(Date#(MonthName, 'MMMM')), 1)) as DateThe 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';