How to derive quarter from YYYYMM date format

Question

Hello,

I am extracting date information from a CSV file that is in a YYYYMM format and I want to derive the calendar quarter from this information.

I attempted to use data conversion to convert the CSV data into a date format but it changes the date to YYYYMMDD format which is incorrect. I am trying to change it to a date format so I can use DATEPART expression to derive the calendar quarter. Is it possible to derive quarter using datepart or should I use a conditional expression?

I am using the Integration Services Project in Visual Studio. Any help is appreciated. Thank you,

Answer 1

Hi @Rose ,

We can get quarter from date using the following expression in Derived Column Transformation:

DATEPART("MM",NewBirthdate)<=3 ? 1 : (DATEPART("MM",NewBirthdate)<=6 ? 2 : (DATEPART("MM",NewBirthdate)<=9 ? 3 : 4))

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

My date is actually only the year and month such as 201201

But that isn't a date, it is a year-month information, as you wrote. Store it as numeric value, e.g. as an integer.

Answer 3

If your datum is stored as a text or integer, then also try the expression that is shown in this example:

declare @column as varchar(6) = '202010' -- or 'int'

select (@column % 100 - 1) / 3 + 1 as [Quarter]

Check if it works in Integration Services.