Concatenate 3 fields SQL Use the SQL Snippet in transformation to concatenate three fields with a space. [Field 1] Field 0 [Field 2] Field 1 [Field 3] Field 2 Concatenate [Field 1] + ' ' + [Field 2] + ' ' + [Field 3] Concatenate 2 fields SQL Use the SQL Snippet in transformation to concatenate two fields with a space. [Field 1] Field This is the 1st field before the space. 0 [Field 2] Field This is the 2nd field after the space. 1 Concatenate [Field 1] + ' ' + [Field 2] Time Table SQL This SQL Snippet should be used in a script action in order to populate a 24 hour time table with 15 minute increments. In order for this script to work, you must first create a dimTime table with the following fields: StartTime(DateTime), EndTime(DateTime), Hour(Int), Minutes(Int), HourText(Varchar), MinuteText(Varchar) [dimTime] Table 0 [StartTime] Field Data Type DateTime 1 [EndTime] Field Data Type DateTime 2 [Hour] Field Data Type Int 3 [Minute] Field Data Type Int 4 [HourText] Field Data Type Varchar 5 [MinuteText] Field Data Type Varchar 6 Date and time --Script written by TimeXtender NA; Tobias Eld, Joseph Treadwell DECLARE @counter datetime SET @counter = 0 WHILE @counter <'1900-01-02 00:00:00.000' BEGIN INSERT INTO [dimTime]([StartTime], [EndTime], [Hour], [Minute]) VALUES (@counter,dateadd(MINUTE,15,@counter),DATEPART(HOUR,@counter),datepart(MI,@counter)) SET @counter = dateadd(MI,15,@counter) END UPDATE [dimTime] SET [HourText] = left(right(convert(nvarchar,dateadd(HH, [Hour], CAST(0 as datetime)),20),8),5), [MinuteText] = left(right(convert(nvarchar,dateadd(HH, [Hour], dateadd(mi,[Minute],CAST(0 as datetime))),20),8),5) +' - '+ left(right(convert(nvarchar,dateadd(HH, [Hour], dateadd(mi,[Minute]+14,CAST(0 as datetime))),20),8),5) Last date of month SQL Returns a datetime value containing the last date of the month based on a datetime value in a field DateParam Field 0 Date and time DATEADD (d, -1, DATEADD (mm, DATEDIFF (m, 0, DateParam) + 1, 0)) First date of year SQL Returns a datetime value containing the first date of the year based on the datetime value of a field DateParam Field 0 Date and time DATEADD (yy, DATEDIFF (yy, 0, DateParam), 0) First date of previous year SQL Returns a datetime value containing the first date of a prevoius year Enter 1 for last year. The value entered determines the number of years to go back. DateParam Field 0 YearDistance Value 1 Date and time DATEADD (yy, DATEDIFF (yy, 0, DateParam) - YearDistance, 0) First date of next year SQL Returns a datetime value containing the first date of next year DateParam Field 0 Date and time DATEADD (yy, DATEDIFF (yy, 0, DateParam) + 1, 0) First date of month SQL Returns a datetime value containing the first date of the month based on a datetime value in a field DateParam Field 0 Date and time DATEADD (mm, DATEDIFF (mm, 0, DateParam), 0) First date of last year SQL Returns a datetime value containing the first date of last year DateParam Field 0 Date and time DATEADD (yy, DATEDIFF (yy, 0, DateParam) - 1, 0) First date of future year SQL Returns a datetime value containing the first date of future year Enter 1 for next year. The value entered determines the number of years to go forward. DateParam Field 0 YearDistance Value 1 Date and time DATEADD (yy, DATEDIFF (yy, 0, DateParam) + YearDistance, 0) DateKey SQL Converts a datetime value to an integer to be used as a date dimension key. DateValue Field This is the date field you wish to convert. 0 Date and time Convert(varchar,DateValue,112) DateDiff (Variable End Date) SQL Returns the number of date and time boundaries crossed between two specified dates. Datepart year: yy, yyyy quarter: qq, q month: mm, m dayofyear: dy, y day: dd, d week: wk, ww Hour: hh minute: mi, n second: ss, s millisecond: ms datepart Value DatePart See Snippet Description for options 0 startdate Field Starting Date - Must be a DateTime field type 1 enddate Field Ending Date - Must be a DateTime field Type 2 Date and time DATEDIFF(datepart,startdate,enddate) DateDiff (Fixed End date) SQL Returns the number of date and time boundaries crossed between two specified dates. Datepart year: yy, yyyy quarter: qq, q month: mm, m dayofyear: dy, y day: dd, d week: wk, ww Hour: hh minute: mi, n second: ss, s millisecond: ms datepart Value DatePart Parameter, See Snippet description for options 0 startdate Field Starting Date - Must be a DateTime field Type 1 Date and time DATEDIFF(datepart,startdate,GetDate()) Current date - Last date of month SQL Returns a datetime value containing the last date of the month based on the current datetim Date and time DATEADD (d, -1, DATEADD (mm, DATEDIFF (m, 0, GETDATE ()) + 1, 0)) Current date - First date of year SQL Returns a datetime value containing the first date of the year based on the current datetime Date and time DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0) Current date - First date of previous year SQL Returns a datetime value containing the first date of a prevoius year Enter 1 for last year. The value entered determines the number of years to go back. YearDistance Value 0 Date and time DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - YearDistance, 0) Current date - First date of next year SQL Returns a datetime value containing the first date of next year Date and time DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + 1, 0) Current date - First date of month SQL Returns a datetime value containing the first date of the month based on the current datetime Date and time DATEADD (mm, DATEDIFF (mm, 0, GetDate ()), 0) Current date - First date of last year SQL Returns a datetime value containing the first date of last year Date and time DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - 1, 0) Current date - First date of future year SQL Returns a datetime value containing the first date of a future year Enter 1 for next year. The value entered determines the number of years to go forward. YearDistance Value 0 Date and time DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + YearDistance, 0) Extract NavCompany SQL [Source Table] Field 0 Dynamics & Navision REPLACE(SUBSTRING([Source Table], CHARINDEX('.', [Source Table])+1, CHARINDEX('$', [Source Table])-CHARINDEX('.', [Source Table])-1), '_', '.') Split Right SQL [ValueToSplit] Field 0 Split SUBSTRING([ValueToSplit],CHARINDEX(' ',[ValueToSplit]),50) Split Left SQL [ValueToSplit] Field 0 Split SUBSTRING([ValueToSplit],1,CHARINDEX(' ',[ValueToSplit]))