MakeDateTime
MakeDateTime | ||
| Description | Creates a date based on the parameters passed into the function. | |
| Syntax | MakeDateTime(Year, Month, Day [, Hour] [, Minute] [, Second] [, Millisecond]) | |
| Returns | returns date | |
| Parameters | ||
| Name | Optional | Description |
| Year | false | Year of the date being created |
| Month | false | Month of the date being created |
| Day | false | Day of the date being created |
| Hour | false | Hour of the date being created |
| Minute | false | Minute of the date being created |
| Second | false | Second of the date being created |
| Millisecond | false | Millisecond of the date being created |
| Examples | ||
| MakeDateTime("2012", "07", "07"), MakeDateTime("2012", "07", "07", "12", "30", "45", "001") | ||
Max(Date)
Max (Date) | ||
| Description | Aggregation that returns max date the group. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Max( Value [, Condition]) | |
| Returns | Date. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Max(SalesDate, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Max(SalesDate), Max(SalesDate, CustomerState="FL") | ||
Max(Number)
Max (Number) | ||
| Description | Aggregation that returns max number the group. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Max( Value [, Condition]) | |
| Returns | Integer. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Max(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Max(SalesTotal), Max(SalesTotal, CustomerState="FL") | ||
Max(String)
Max (String) | ||
| Description | Aggregation that returns max string the group. The value is based on the ascii value of the string. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Max( Value [, Condition]) | |
| Returns | String | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Max(CustomerAlternateKey, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Max(CustomerAlternateKey), Max(CustomerAlternateKey, CustomerState="FL") | ||
MD5
MD5 | ||
| Description | Calculates the checksum of the input value. The function uses Message-Digest algorithm 5 (MD5). MD5 is a one- way cryptographic hash function with a 128-bit hash value. You can conclude that input values are different when the checksums of the input values are different. Use MD5 to verify data integrity. | |
| Syntax | MD5( Value ) | |
| Returns | Unique 32-character string of hexadecimal digits 0-9 and a-f. NULL if the input is a null value. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | String or Binary datatype. Value for which you want to calculate checksum. The case of the input value affects the return value. For example, MD5(ssis) and MD5(SSIS) return different values. |
| Examples | ||
| MD5("secure this") | ||
MessageBox
MessageBox | ||
| Description | Displays a MessageBox with a message (Only works in BIDS / SQL Server Data Tools). Used for debugging purposes during design time. | |
| Syntax | MessageBox(Message [, Condition]) | |
| Returns | Nothing | |
| Parameters | ||
| Name | Optional | Description |
| Message | false | The message to display. |
| Condition | true | The condition to evaluate to true before display the MessageBox |
| Examples | ||
| MessageBox("This is a message"), MessageBox("Values didn't match", value1!=value2)] | ||
MilliSecond
MilliSecond | ||
| Description | Returns the MilliSecond from the date specified. | |
| Syntax | MilliSecond( Date ) | |
| Returns | returns integer value of 0 - 999 | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve MilliSecond from |
| Examples | ||
| MilliSecond("12/8/2011"), MilliSecond(varDate), MilliSecond(OrderDateColumn) | ||
Min (Date)
Min (Date) | ||
| Description | Aggregation that returns Min date the group. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Min( Value [, Condition]) | |
| Returns | Date. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Min(SalesDate, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Min(SalesDate), Min(SalesDate, CustomerState="FL") | ||
Min (Number)
Min (Number) | ||
| Description | Aggregation that returns Min number the group. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Min( Value [, Condition]) | |
| Returns | Integer. | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Min(SalesTotal, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Min(SalesTotal), Min(SalesTotal, CustomerState="FL") | ||
Min (String)
Min (String) | ||
| Description | Aggregation that returns Min string the group. The value is based on the ascii value of the string. Can only be used as part of the Advanced Aggregation component | |
| Syntax | Min( Value [, Condition]) | |
| Returns | String | |
| Parameters | ||
| Name | Optional | Description |
| Value | false | The value to add to the aggregation |
| Condition | false | The condition allows you to conditionally add values to the aggregation based on the expression. For instance, you could define the aggregate as Min(CustomerAlternateKey, CustomerState="FL") and this would only add the value to the aggregation if the customers state was florida. |
| Examples | ||
| Min(CustomerAlternateKey), Min(CustomerAlternateKey, CustomerState="FL") | ||
Minute
Minute | ||
| Description | Returns the Minute from the date specified. | |
| Syntax | Minute( Date ) | |
| Returns | returns integer value of 0 - 59 | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve Minute from |
| Examples | ||
| Minute("12/8/2011"), Minute(varDate), Minute(OrderDateColumn) | ||
Mod
Mod | ||
| Description | Divides two numbers and returns the remainder. | |
| Syntax | Mod( Value1, Value2 ) | |
| Returns | Numeric | |
| Parameters | ||
| Name | Optional | Description |
| Value1 | false | The first number in the equation. |
| Value2 | false | The second number in the equation. |
| Examples | ||
| Mod(10, 3) | ||
Month
Month | ||
| Description | Returns the Month from the date specified. | |
| Syntax | Month( Date ) | |
| Returns | returns integer value of 1 - 12 | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve Month from |
| Examples | ||
| Month("12/8/2011"), Month(varDate), Month(OrderDateColumn) | ||
MonthName
MonthName | ||
| Description | Returns the name of the Month (MonMonth, TuesMonth, etc) from the date specified. | |
| Syntax | MonthName( Date ) | |
| Returns | returns string. Name of the Month | |
| Parameters | ||
| Name | Optional | Description |
| Date | false | Date to retrieve Month name from |
| return_shortname | false | Return the short name of the Month (Jan, Feb, Mar) |
| Examples | ||
| MonthName("12/8/2011", false), MonthName(varDate, True), MonthName(OrderDateColumn, false) | ||
Now
Now | |
| Description | Returns the current date and time. |
| Syntax | Now() |
| Returns | Date |
| Examples | |
| Now() | |
NullIf
NullIf | ||
| Description | Compares two string values for equality and returns null if they match. | |
| Syntax | NullIf( String_Value1, String_Value2 ) | |
| Returns | Null if "String_Value1" or "String_Value2" is null. Returns value of "String_Value1" if "String_Value2" and "string_value2" do not match.. | |
| Parameters | ||
| Name | Optional | Description |
| String_Value1 | false | Any string value. If the values do not match, this value is returned from the function. |
| String_Value2 | false | Any string value. |
| Examples | ||
| NullIf(CompanyNameColumn, ""), NullIf(AddressColumnLine2, "") | ||
NullIfEmpty
NullIfEmpty | ||
| Description | Checks a string value for an empty value. If the string value is empty a null value is returned. | |
| Syntax | NullIfEmpty( String_Value ) | |
| Returns | Null if "String_Value" is empty. Returns value of "String_Value" if the value is not empty. | |
| Parameters | ||
| Name | Optional | Description |
| String_Value | false | Any string value. |
| Examples | ||
| NullIfEmpty(CompanyNameColumn), NullIfEmpty(varValue) | ||
