EDUCBA

Home Data Science Data Science Tutorials Data Analytics Basics SSIS DATEPART

SSIS DATEPART

Updated March 16, 2023

SSIS DATEPART

Definition of SSIS DATEPART

The following article provides an outline for SSIS DATEPART. The DATEPART() function extracts a portion of a certain date, a day, month, year, or time. It generates an integer that is a component of a date every time. To which part of the date should the supplied number be added? For example, year (yy, yyyy), a quarter (qq, q), month (mm, m), day of the year (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (mm, m) are all valid values and acronyms (ms).

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is SSIS DATEPART?

The syntax goes like this:

DATEPART(datepart, date)
  • datepart: This parameter defines which portion of the date should be used to generate a new integer.
  • date: Is a function that returns a valid date or a date-formatted text.

The weekday number was calculated using the calculation diff % 7 + 1. So, for example, assuming Monday is the first day of the week, we can use the phrase to find the weekday of today’s date.

SELECT DATEDIFF(day, 0, GETDATE ()) %7 + 1;

The above query would return two if today were a Tuesday. In this post, I prefer a date diff-based method.

The SSIS formula below returns a YYYYMMDDHHMMSS style 14-character timestamp from a Date Time (for usage in a variable or generated column, for example). The current date and time are used in this instance; for different datetimes, change getdate() with the variable/column/expression having the relevant value. SSIS expression plays a vital role here. SSIS expressions are a collection of literals, procedures, and operators that produce a single data value. A specific value or composite kind can be used to make a statement.

(DT_STR,4,1252)DATEPART( "yyyy" , getdate() )
+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , getdate() ), 2)
+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , getdate() ), 2)
+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "Hh" , getdate() ), 2)
+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , getdate() ), 2)
+ RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , getdate() ), 2)

The above expression is a char count with 1252 cod pages and casting to another data type.

Expressions are a formula that can be used to calculate a value depending on a list of requirements. In this scenario, I’ll construct the file name for a file connection using an expression based on the current date in the format YYYYMMDD.

To demonstrate, I’ve written a simple SSIS package that executes a SQL statement (i.e., calculating the length of each database) and saves the findings to a file. There is only one Data Flow Task in the package:

k

When we switch to the Information Flow tab, you’ll notice that the job contains an OLE DB Source that utilizes a SQL Server connectivity, as well as a Flat File Endpoint that utilizes a File Connection:

o

v

The above Expression is given in the same way which is shown above:

"C:\\DatabaseSizes_" + (DT_WSTR,4) DATEPART("yyyy",GetDate()) +
RIGHT ("0" + (DT_WSTR,2) DATEPART("mm",GetDate()) ,2) +
RIGHT ("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + ".csv"

This will generate a file name like: C:\Data123.csv.

Next, to include a time in a file name, the other way expression is:

"C:\\Datas_" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
RIGHT ("0" + (DT_WSTR,2) DATEPART("dd",GetDate()),2) + "_" +
RIGHT ("0" + (DT_WSTR,2) DATEPART("hh",GetDate()),2)+
RIGHT ("0" + (DT_WSTR,2)DATEPART("mi",GetDate()),2) + ".csv"

SSIS DATEPART function

Time from DateTime:

If we want to delete the datepart from a datetime object, convert it to DT DBTIME. Optionally, we can cast it to a string.

(DT_STR,8,1252)(DT_DBTIME)@[User::datetimeVar]
(DT_STR,8,1252)(DT_DBTIME)[datetimeCol]
(DT_STR,8,1252)(DT_DBTIME)GETDATE()

Choose a day for the week’s start.

The SSSIS DATEFIRST function can be used to determine the first day of the week. One can choose from 1 to 7 as a value. Whenever the value one is specified, Monday is the first day of the week. For SSIS DATEFIRST, one can use the table below to specify a value.

1. To calculate the number of days in the quarter of date field. Here the files are dtf.

For example

If the dtf is "2022-03-04" the count will be 60
If the dtf is "2022-06-20" the count will be 80

expression:

DATEDIFF("DAY",(DT_DATE)(((DT_WSTR,4)YEAR(dtf)) + "-" + ((DT_WSTR,2)(((DATEPART("QUARTER",dtf) – 1) * 3) + 1)) + "-1"),dtf)

2. To calculate the quarter number of a date

For example

If the Dtf is “2022-03-04,” the quarter count will be 2
If the Dtf is “2022-06-20,” the quarter count will be 3
If the Dtf is “2022-10-06,” the quarter count will be 5

(DT_WSTR,1)(DATEPART("QUARTER",Dtf))

SSIS DATEPART examples

In this part, let’s look at DATEPART SSIS with examples and interval values.

Month-Name and Day-Name are not included in the SSIS Package. In our module, we may construct two parameters and create expressions on them until we use them whenever Month Name and Day Name are required.

To get the Month’s name.

SUBSTRING("January February March April May June July August September October November December ",((DATEPART("MONTH",GETDATE())-1)*10)+1,10)
SUBSTRING("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec ",((DATEPART("MONTH",GETDATE())-1)*4)+1,3)

Next, taking out to find the day or a week in SSIS, the below expression is given as :

(DT_STR,20,1252)((DATEPART("DW",[Transaction date]) == 7) ? "SUNDAY" :
(DATEPART("DW",[Transaction date]) == 1) ? "MONDAY" :
(DATEPART("DW",[Transaction date]) == 2) ? "TUESDAY" :
(DATEPART("DW",[Transaction date]) == 3) ? "WEDNESDAY" :
(DATEPART("DW",[Transaction date]) == 4) ? "THURSDAY" :
(DATEPART("DW",[Transaction date]) == 5) ? "FRIDAY" :
(DATEPART("DW",[Transaction date]) == 6) ? "SATURDAY" : "NULL")

How can I retrieve the date for a given weekday in SSIS? How can I get last Tuesday’s date, for instance?

To get the date for every given day in the current week, we could use the following command:

DECLARE @DesiredDay INT = 0;
DECLARE @OutputDate DATE;
SET DATEFIRST 2; /* Tuesday is day 2 */
SET @OutputDate = DATEADD(d, @DesiredDay - DATEPART(DW, GETDATE()), GETDATE());
SELECT @OutputDate;

To get Year\Month\Day

@[User::Folder] + "\\"
+ (DT_WSTR, 4)YEAR(GETDATE())
+ "\\" + Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),3)
+ "\\" + Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),3)

This Returns -\\Folder\2022\02\02

Simple Year Month Day

"P_File"
+ REPLACE(SUBSTRING((DT_STR, 30, 1252)GETDATE(), 1, 9), "-", "")
+ ".txt"

This Returns P_File2022\02\10

Conclusion

Coming to an end, we have seen how the Date part plays a significant role in Date Time functions in SSIS. And we have seen SSIS expression to evaluate the respective day/week in files and other things.

Recommended Articles

This is a guide to SSIS DATEPART. Here we discuss the definition, What SSIS DATEPART is, and Examples with code implementation. You may also have a look at the following articles to learn more –

  1. SSIS
  2. SSIS Interview Questions
  3. Talend vs. SSIS
  4. Redshift DateDiff
quiz