SSIS Convert a string into a datehttps://www.joellipman.com/articles/microsoft/ssis/ssis-convert-a-string-into-a-date.html

14 March 2013 : This_String ISNULL(This_Date) ? NULL(DT_DATE) : (DT_DATE)This_Date ISNULL(This_Date) ? "" : (DT_STR)This_Date -- if blanks LTRIM(This_String)=="" ? "" : LTRIM(This_String) LTRIM(This_Date)=="" ? (DT_DATE)"1900-01-01" : (DT_DATE)This_Date -- last...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Integration Services
• Language: en-GB

T-SQL functions to convert Strings to Tableshttps://www.joellipman.com/articles/database/t-sql/t-sql-functions-to-convert-strings-to-tables.html

20 August 2014 BEGIN DECLARE @StringValue VARCHAR(10) SET @StringInput = RTRIM(LTRIM(@StringInput)) WHILE LEN(@StringInput) > 0 BEGIN SET @StringValue = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(' ', @StringInput) - 1, -1), LEN(@StringInput))) SET @StringInput =...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

Stored Procedure to List Distinct Values and Countshttps://www.joellipman.com/articles/database/t-sql/stored-procedure-to-list-distinct-values-and-counts.html

26 October 2012 Cursor1 FETCH NEXT FROM Cursor1 INTO @ColToProcess WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlToExecute = ' SELECT DISTINCT ' + LTRIM(@ColToProcess) + ' COLLATE ' + @p_UseCollation + ', ''' + LTRIM(@ColToProcess) + ''', COUNT(*) AS Count FROM ' +...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

DataJumble - Shuffling characters in a data valuehttps://www.joellipman.com/articles/database/t-sql/data-shuffling-function.html

01 August 2012 DECLARE @TimeMatch int; DECLARE @ColumnTypeIsDate int; -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen = DATALENGTH(@OrigVal); SET @CurrLen =...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: *

DataScramble - Randomizing data rowshttps://www.joellipman.com/articles/database/t-sql/datascramble-randomizing-data-rows.html

07 December 2012 @IntOptions int; DECLARE @CharOptions varchar(max); -- Format given value (prevents CHAR datatype issue) SET @OrigVal = LTRIM(RTRIM(@OrigVal)); -- Set variable default values SET @NewVal = ''; SET @OrigLen = LEN(CAST(@OrigVal AS VARCHAR(max))); SET...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

T-SQL Conversion failed when converting the varchar to data type inthttps://www.joellipman.com/articles/database/t-sql/t-sql-conversion-failed-when-converting-the-varchar-to-data-type-int.html

14 May 2013 above and you get half a million results. That's a lot of reading so here's the simple fix for my issue: Returning an error: LTRIM(RTRIM(rm.Name + ', ' + rm.[Zone] + ', ' + rm.[Capacity] + ', ' + rm.[Type] + ', ' + rm.[DeptId])) AS RoomDetails // yields...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB

SSRS Querying on either of 2 Parametershttps://www.joellipman.com/articles/microsoft/ssrs/ssrs-querying-on-either-of-2-parameters.html

19 July 2011 This is obviously going to error, so continuing on their solution, setting the "Available Values – Specify values" to =IIF(LTrim(Parameters!StudentADAccount.Value)="", Parameters!StudentReference.Value, First(Fields!S_STUDENTREFERENCE.Value,...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: en-GB

Search a database for a string (MySQL, T-SQL)https://www.joellipman.com/articles/database/search-a-database-for-a-string-mysql-t-sql.html

08 October 2012 VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO @CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT...

• Type: Article
• Author: Joel Lipman
• Category: Databases
• Language: en-GB

Convert to Proper Case in T-SQLhttps://www.joellipman.com/articles/database/t-sql/convert-to-proper-case-in-t-sql.html

04 June 2014 next word SET @i = CHARINDEX(',', @CaseExceptions, @i+@CaseLen) +1 END -- remove any leading and trailing spaces SET @Ret = LTRIM(RTRIM(@Ret)); -- capitalize first character of data irrespective of previous rules SET @Ret = UPPER(SUBSTRING(@Ret,1,1)) +...

• Type: Article
• Author: Joel Lipman
• Category: Transact-SQL
• Language: en-GB