Converting Month Number(Date Time or 4 byte integer) to Month Name(String) SSIS

Question

I need to convert month number to month name. I have date time as the date type - 2009-01-01 00:00:00.000 I also have 4-byte integer data type - 1

how do I convert this 1 to "January" for example?

Answer 1

i think you are in the data flow:

it is really easy to get MOnth Name in a script component from Date:

1. add a varchar column to your dataflow

2. Mark your date column for read access

3. enter the following script

   Row.[NewColumnName] = Row.[Your Date Column].ToString("MMMM");
   

Result:

Here is a good translations for any date part to string formatting:

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone

Furthermore, you asked about quarters. I don't think it is as easy but here is something I stole from another answer.

Build DateTime extensions:

Normal Quarter:

public static int GetQuarter(this DateTime date)

{
    return (date.Month + 2)/3;
}

Financial Year Quarter (This case is for quarters that start on April 1):

public static int GetFinancialQuarter(this DateTime date)
{
    return (date.AddMonths(-3).Month + 2)/3;
}

Integer division will truncate decimals, giving you an integer result. Place methods into a static class and you will have an extension method to be used as follows:

Row.calendarQuarter = Row.[your Date Column].GetQuarter()
Row.fiscalQuarter = Row.[your Date Column].GetFinancialQuarter()

Answer 2

In SQL Server, one method is:

select datename(month, datefromparts(2000, 1, 1))

The first "1" is the column for the month. The year is arbitrary.

Answer 3

following steps:

• create variable with datetime datatype and assigned value.
• used MONTH function in ssis to extract month number and assigned to new variable with integer data type: @[User::newdata]= MONTH( @[User::dbdate])
• finally used if else condition which manually compare all 12 months (code available:1)