How to derive quarter from YYYYMM date format

Rose 21 Reputation points
2020-10-23T02:49:45.727+00:00

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,

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,440 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,417 questions
0 comments No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,891 Reputation points
    2020-10-23T03:43:13.113+00:00

    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))

    34377-getquarter.png

    34378-dfoutput.png

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2020-10-23T07:08:45.553+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Viorel 110.1K Reputation points
    2020-10-23T07:59:11.987+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments