Wednesday, March 23, 2016

Convert month number into month name in derived column expression

Scenario 1: Sometimes we need to convert (or) we need to extract month name from the date time datatype (or) any date format by using derived column or in expression task

Syntax for changing date to month name:-
(MONTH(getdate()) == 1 ? "January" :
 MONTH(getdate()) == 2 ? "February" : 
 MONTH(getdate()) == 3 ? "March" :
 MONTH(getdate()) == 4 ? "April" :
 MONTH(getdate()) == 5 ? "May" : 
 MONTH(getdate()) == 6 ? "June" :
 MONTH(getdate()) == 7 ? "July" :
 MONTH(getdate()) == 8 ? "August" :
 MONTH(getdate()) == 9 ? "September" : 
 MONTH(getdate()) == 10 ? "October" : 
 MONTH(getdate()) == 11 ? "November" : 
 MONTH(getdate()) == 12? "December":"")
Replace column name as per your table column name , here i replaced with [date] 


Place below expression in derived column expression area:-
(MONTH([date]) == 1 ? "January" : MONTH([date]) == 2 ? "February" : MONTH([date]) == 3 ? "March" : MONTH([date]) == 4 ? "April" : MONTH([date]) == 5 ? "May" : MONTH([date]) == 6 ? "June" : MONTH([date]) == 7 ? "July" : MONTH([date]) == 8 ? "August" : MONTH([date]) == 9 ? "September" : MONTH([date]) == 10 ? "October" : MONTH([date]) == 11 ? "November" : MONTH([date]) == 12? "December":"")



Scenario 2: Sometimes we need to convert (or) we need to extract day name from the date time datatype 

Syntax for changing date to day name:-
(DATEPART("dw", GETDATE())==1 ? "Sunday" :
DATEPART("dw", GETDATE())==2 ? "Monday" :
DATEPART("dw", GETDATE())==3 ? "Tuesday" :
DATEPART("dw", GETDATE())==4 ? "Wednesday" :
DATEPART("dw", GETDATE())==5 ? "Thursday" :
DATEPART("dw", GETDATE())==6 ? "Friday" :
DATEPART("dw", GETDATE())==7 ? "Saturday" : "") 

Place below expression in derived column expression area :-
(DATEPART("dw", [date])==1 ? "Sunday" : DATEPART("dw", [date])==2 ? "Monday" : DATEPART("dw", [date])==3 ? "Tuesday" : DATEPART("dw", [date])==4 ? "Wednesday" : DATEPART("dw", [date])==5 ? "Thursday" : DATEPART("dw", [date])==6 ? "Friday" : DATEPART("dw", [date])==7 ? "Saturday" : "")