How to get Day/Week/Month/Year/DayOfYear/WeekDay/Hour/Min/Sec .etc from DateTime

It’s always been tricky for T-SQL Developer to Datepart the Date. With DATENAME Function it has become easy and manageable.

DATENAME can be used for below Datepart

YEAR QUARTER MONTH DAYOFYEAR DAY WEEK WEEKDAY HOUR MINUTE SECOND MILLISECOND MICROSECOND NANOSECOND TZOFFSET

 

SELECT convert(datetime,convert(varchar(10),GETDATE(),101)) Date_As, Day(GETDATE())Day_As,MONTH(GETDATE()) Month_As,YEAR(GETDATE())Year_As, DATENAME(DW,GETDATE()) DayName_As, DATENAME(MONTH,GETDATE())MonthName_As,DATENAME(QUARTER,GETDATE()) QuarterNo_As

 

Examples

 

 

 

Using SSIS Package Configuration Values as Parameters to Execute SQL Tasks

 

Working on the VLWHDB the team came across a very interesting case where customer request was not to limit the [Execute SQL Task] Query to non-static Data source and Database (Dynamic Data source and Dynamic Database) based on SSIS Package Configuration file.

Let’s go over the steps how we can do this. Create a SSIS Project with a Package

Create variables and package configuration file, these variable will be used to pass the value from the Package configuration file to the SQL statement with in [Execute SQL Task].

Drop [Execute SQL Task] on the Package Canvas, Set the Source and Destination Connection. We have populated these connections with some test servers strings just for value purpose.

Configure the Connection with the connection variables as shown in the below image

Now we need to configure the [Execute SQL Task]. To configure the SQL Statement within the [Execute SQL Task] there are 3 different ways

1. Directly input the query in the SQL Statement.
   
2. Build the Query Within the variable
   
3. File Connection
   

Apart from these options there is one more way to build Dynamic Query .Edit the [Execute SQL Task] property navigate to the Expressions tab

Browse the expressionsàProperty Page will Popup.

Select the “SqlStatementSource” Property

Write the Query in the expression Table within Double Quotes (“”) with concatenation (+) to provide the Package Variable Value and Evaluate the Query.
Sample

“INSERT INTO “+ @[User::cfgDestinationDB] + “.dbo.TableName”

 

Once the Query is evaluated without Error the same will be applied to the expression

Save the [Execute SQL Task] Property by clicking “OK” and close the Editior.Save the Package and open the [Execute SQL Task] editor Property.

The Evaluated Expression Query is applied with Package Variable Value to SQL Statement.