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

14 March 2013 Column" tasks for this after giving up with a "Data Conversion" task. I then parse the date, month and year out using SUBSTRING: -- Assuming [This_Date]="21/03/2012" (DT_DATE)(This_Date) -- Assuming [This_Date]="20120321" (YYYYMMDD)...

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

Zoho Creator/Deluge: Calculating with Timezone Offsethttp://mail.joellipman.com/articles/crm/zoho/zoho-creator-deluge-calculating-with-timezone-offset.html

07 December 2021 = "(+01:00) Europe/Luxembourg"; v_CustomerOffset = "+01:00"; // // calculate GMT v_CustomerPlusMinus = v_CustomerOffset.subString(0,1); v_CustomerHours = v_CustomerOffset.subString(1,3).toLong(); v_CustomerMinutes =...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Generate Academic Calendar using MySQLhttp://mail.joellipman.com/articles/database/mysql/generate-academic-calendar-using-mysql.html

27 November 2012 for CMIS Facility SELECT calendar.WeekNumber AS AcademicWeek, CAST(DATEPART(dd, calendar.StartDate) AS VARCHAR(2)) + '-' + SUBSTRING(DATENAME(mm, calendar.StartDate), 1, 3) + '-' + CAST(DATEPART(yyyy, calendar.StartDate) AS VARCHAR(4)) AS Monday,...

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

Zoho Deluge - Compare time and currenttime with timezonehttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-compare-time-and-currenttime-with-timezone.html

13 January 2020 HH:mm:ss"); v_ExpiryTime = "2020-03-25T21:41:07+00:00"; v_ExpiryDatePart = v_ExpiryTime.subString(0,10); v_ExpiryTimePart = v_ExpiryTime.subString(11,19); v_ExpiryTimeCheck = v_ExpiryDatePart + " " + v_ExpiryTimePart; info v_CustomTime.toTime(); info...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Migrate Joomla! 1.5.x to 2.5.x+http://mail.joellipman.com/articles/cms/joomla/migrating-from-joomla-15-to-16.html

30 January 2011 assets: -- - Import banner categories from J15 and insert as assets in J30 SET @new_jos_assets_last_inc=( SELECT (SUBSTRING_INDEX(`name`, '.', -1)*1) AS lastcatid FROM `my_new_database`.my_prefix_assets WHERE `name` LIKE '%.category.%' ORDER BY...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: *

Migrating from Joomla 2.5.x to 3.4.xhttp://mail.joellipman.com/articles/cms/joomla/migrating-from-joomla-2-5-x-to-3-4-x.html

12 September 2015 t2.`extension`='com_content' SET c1.`catid`=t1.`id`; -- Joe-Code: LIST Rows to be affected (for test purposes) -- SELECT SUBSTRING(a.`name`, 1, INSTR(a.`name`, ':|joe')-1 ) -- FROM `v34_db`.`v34_assets` a -- WHERE a.`name` LIKE '%:|joe|:%' -- Joe-Code:...

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

SSIS Script: convert UPPERCASE to Mixed-Case using TitleCasehttp://mail.joellipman.com/articles/microsoft/ssis/ssis-script-convert-uppercase-to-mixed-case-using-titlecase.html

04 October 2013 foreach (string myValue in exceptionArray_this) { if (ValueToConvert.Contains(myValue)) { string McValue = ValueToConvert.Substring(0, ValueToConvert.IndexOf(myValue)) + myValue; McValue += ValueToConvert.Substring((ValueToConvert.IndexOf(myValue) +...

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

Zoho Deluge - Generate 5 Letter Booking Retrieval Codehttp://mail.joellipman.com/articles/crm/zoho/zoho-deluge-generate-5-letter-booking-retrieval-code.html

09 May 2019 method and it will return a string of 5 random uppercase letters. Method #2: Convert time to letters I've previously used substring to extract the milliseconds into 5 numbers and get the remainder from dividing it by 26 (modulus)... This is NOT to be...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

CharIndex Reverse - find occurrence starting from end of string in TSQLhttp://mail.joellipman.com/articles/database/t-sql/charindex-reverse-find-occurrence-starting-from-end-of-string-in-tsql.html

28 January 2013 I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form. For argument's sake, I'm assigning this string to the variable "haystack". How? Perhaps we...

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

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

01 August 2012 does not recognize CAST AS DATE) IF @OrigLen=10 AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1 AND ISNUMERIC(SUBSTRING(@OrigVal, 6, 2))=1 AND ISNUMERIC(SUBSTRING(@OrigVal, 9, 2))=1 SET...

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

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

07 December 2012 does not recognize CAST AS DATE) IF @OrigLen=10 AND (LEN(@OrigVal) - LEN(REPLACE(@OrigVal, '-', '')))=2 AND ISNUMERIC(SUBSTRING(@OrigVal, 1, 4))=1 AND ISNUMERIC(SUBSTRING(@OrigVal, 6, 2))=1 AND ISNUMERIC(SUBSTRING(@OrigVal, 9, 2))=1 SET...

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

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

04 June 2014 WHILE (@i 0 BEGIN -- get the delimited word SET @CaseLen = CHARINDEX(',', @CaseExceptions, @i+1) - @i SET @CaseValue = SUBSTRING(@CaseExceptions, @i, @CaseLen) -- replace it in the original text SET @Ret = REPLACE(@Ret, ' '+@CaseValue+' ', '...

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

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

20 August 2014 = LEFT(@StringInput, ISNULL(NULLIF(CHARINDEX(' ', @StringInput) - 1, -1), LEN(@StringInput))) SET @StringInput = SUBSTRING(@StringInput, ISNULL(NULLIF(CHARINDEX(' ', @StringInput), 0), LEN(@StringInput)) + 1, LEN(@StringInput)) SET @StringInput =...

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

ZohoCreator: Using .toFile and Uploading to a Creator fieldhttp://mail.joellipman.com/articles/crm/zoho/zohocreator-using-tofile-and-uploading-to-a-creator-field.html

25 March 2024 bigger than 64kb c_Check.Cached_HTML = v_HtmlToCache; if(v_HtmlToCache.len()>65000) { c_Check.Cached_HTML = v_HtmlToCache.subString(0,65000); c_Check.Cached_HTML_2 = v_HtmlToCache.subString(65000); } if(v_HtmlToCache.len()>129999) {...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Zoho Survey & Zoho Analytics: Query to generate individual responses and grouped pageshttp://mail.joellipman.com/articles/crm/zoho/zoho-survey-zoho-analytics-query-to-generate-individual-responses-and-grouped-pages.html

25 September 2025 be associated with an email address. If not, then see the 2nd snippet of code. SELECT concat('REF-', left_pad(to_integer(substring(r2."ID", 8)), 6, '0')) AS "Attempt ID", s."Title" AS "Survey", r2."End Date" AS "Response Date", CASE p."Title" WHEN...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

Split a row into multiple rows based on a column valuehttp://mail.joellipman.com/articles/database/t-sql/split-a-row-into-multiple-rows-based-on-a-column-value.html

23 August 2013 we want the rows to multiply based on the value of the column: SELECT EmployeeNo , DaysOffSick , DateOfSickness ,'1' + substring(CAST(DaysOffSick AS VARCHAR(10)), 2, 1000) AS Items FROM Employees_Attendance_Table JOIN master..spt_values n ON n.type =...

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

MySQL parameters in Excel 2007 PivotTableshttp://mail.joellipman.com/articles/database/mysql/mysql-parameters-in-excel-2007-pivottables.html

24 August 2011 CONCAT(r.CapexCode, ' - ', r.ResourceName) AS Resource, s.StaffActivity AS Activity, s.AdditionalNotes AS Notes, CONCAT(SUBSTRING(YEARWEEK(s.DateTimeCreated,1) FROM 5), '/', SUBSTRING(YEARWEEK(s.DateTimeCreated,1), 1, 4)) AS ActivityWeek, CASE COUNT(*)...

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

Migrate JComments from Joomla 1.5.x to Joomla 2.5.xhttp://mail.joellipman.com/articles/cms/joomla/migrate-jcomments-from-joomla-15x-to-joomla-25x.html

07 June 2012 ispoor, published, subscribe, source, source_id, checked_out, checked_out_time, editor ) SELECT id, parent, CASE WHEN SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2))='' THEN 0 ELSE SUBSTRING(path, LOCATE(',', path)+1, LOCATE(',', path, 2))...

• Type: Article
• Author: Joel Lipman
• Category: Joomla
• Language: *

Zoho CRM/Creator - Common Errors & Gotchashttp://mail.joellipman.com/articles/crm/zoho/zoho-crm-creator-common-errors-gotchas.html

06 February 2020 but we're missing the colon in the TimeZone value: v_TimeZone = zoho.currentdate.toString("Z"); v_TimeZoneStr = v_TimeZone.substring(0,3) + ":" + v_TimeZone.substring(3); v_InsertDateTime = zoho.currenttime.toString("yyyy-MM-dd'T'HH:mm:ss") +...

• Type: Article
• Author: Joel Lipman
• Category: Zoho
• Language: *

MySQL: Count occurrences of words in a columnhttp://mail.joellipman.com/articles/database/mysql/mysql-count-occurrences-of-words-in-a-column.html

12 March 2020 ( SELECT count(*) AS `total_count`, REPLACE(REPLACE(REPLACE(x.`value`,'?',''),'.',''),'!','') as `value` FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(t.`introtext`, '//text()'), ' ', n.n), ' ', -1) `value` FROM `mydbprefix_content` t CROSS...

• Type: Article
• Author: Joel Lipman
• Category: MySQL
• Language: *