T-SQL DateTimestampshttps://joellipman.com/articles/database/t-sql/t-sql-datetimestamps.html

23 March 2011 2008 SQL Server 2008 R2 Regional Setting: London BST (British Summer Time) Tuesday the 11th of October 2011 at 06:00am: CONVERT(CHAR(19), CURRENT_TIMESTAMP, 0) Oct 11 2011 6:00AM CONVERT(CHAR(8), CURRENT_TIMESTAMP, 1) 10/11/11 CONVERT(CHAR(8),...

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

SQL Calendar in Business Intelligence Development Studiohttps://joellipman.com/articles/database/t-sql/sql-calendar-in-business-intelligence-development-studio.html

14 January 2011 @EndDate = DATEADD(DAY,6-DATEPART(WEEKDAY,@EndDate),@EndDate) ; WITH Dates([Date]) AS ( --Select First day in range SELECT CONVERT(DATETIME,@StartDate) AS [Date] UNION ALL --Add a record for every day in the range SELECT DATEADD(DAY, 1, [Date]) FROM...

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

Convert XML UTF-16 to JSON UTF-8 with PHP cURLhttps://joellipman.com/articles/web-development/php/convert-xml-utf-16-to-json-utf-8-with-php-curl.html

28 March 2015 PHP cURL: parser error: Document labelled UTF-16 but has UTF-8 content What? This is an article with notes for me on how to convert some received XML encoded in UTF-16 to some JSON in UTF-8. If it were entirely in UTF-8, I would simply load the received...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Preg_Replace all strings between two tagshttps://joellipman.com/articles/web-development/php/preg-replace-all-strings-between-two-tags.html

07 February 2013 examples below are meant to help but feel free to comment if they do not: Surround string (apostrophes) with red styling Convert: this='a string' » this='a string' $string_formatted = preg_replace ( '/\'([^\']+)\'/', '$0', $string_original ); Surround...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: *

Zoho Deluge: Nested Catch Statementshttps://joellipman.com/articles/crm/zoho/zoho-deluge-nested-catch-statements.html

04 February 2025 and to deal with a scenario which exists for a client of mine. The use-case for my Cx is that they need documents converted from HTML to PDF — as in we send it a bit of code in HTML and we want a PDF file returned. I have severaly instances of an API...

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

MS Excel: Convert a text to a numberhttps://joellipman.com/articles/microsoft/excel/ms-excel-convert-a-text-to-a-number.html

15 November 2013 This is a quick article on how to convert some cells in Microsoft Excel to number values... Why? OMG. Seriously Microsoft! I have spent an hour trying to convert a column of currency values to a number using Microsoft Excel 2010. Since when did MS Excel...

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

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

14 March 2013 article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was...

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

The ReportServer Databasehttps://joellipman.com/articles/database/the-reportserver-database.html

08 August 2011 of rendered reports in bytes. RowCountbigintNOT NULLNumber of rows returned from queries. Conversions Needed: I want to convert a start and end date to a single time value (hours minutes seconds mseconds) I want to total 3 of the columns (which measure...

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

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

04 June 2014 So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles...

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

ZohoCRM: Daily Follow Up and Remind Record Owner to Convert Leadhttps://joellipman.com/articles/crm/zoho/zohocrm-follow-up-and-remind-record-owner-to-convert-lead.html

14 May 2024 article to ensure I never spend this long on such a request again. The brief is: "Follow up when a lead is created and not converted within 1 day send an email and notification to sales person everyday for 3 days then escalate to a specified user". Why?...

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

Zoho Deluge: Convert Hex to RGBhttps://joellipman.com/articles/crm/zoho/zoho-deluge-convert-hex-to-rgb.html

11 October 2024 A quick article to document a Zoho Deluge function converting a hexadecimal color reference to a Red Green Blue value (RGB). Why? It's likely that Zoho will avail their color picker at some point as an input but at the time of print, this hasn't...

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

Convert a delimited string to tablehttps://joellipman.com/articles/database/convert-a-delimited-string-to-table.html

26 October 2012 This is a quick note to show you how to convert a given comma delimited string into a database table: Given: "Title,Forenames,Surname" Return: ID Value ------ ---------------- 1 Title 2 Forenames 3 Surname Note the below example omits the ID column and...

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

List MediaWiki Articleshttps://joellipman.com/articles/cms/mediawiki/list-mediawiki-articles.html

22 September 2011 query here: The base query to list mediawiki articles Page ID, Title, Content, Category SELECT p.page_id AS PageID, CONVERT(p.page_title USING latin1) AS PageTitle, CONVERT(t.old_text USING latin1) AS PageContent, (SELECT...

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

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

04 October 2013 the script into a reusable component. Example #1: As an SSIS Script Task My solution so far: The following script will convert to lowercase and then capitalize the first letter in addition to the above specification. I am storing it here so that I can...

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

Excel: convert degrees minutes seconds to decimalhttps://joellipman.com/articles/microsoft/excel/excel-convert-degrees-minutes-seconds-to-decimal.html

06 November 2014 40.079857, 116.603112. How? Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert: A B ------------------------------------- ---------------------- Beijing Capital International Airport 40°4′20″N...

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

Convert Past Date to Time Ago in PHPhttps://joellipman.com/articles/web-development/php/convert-past-date-to-time-ago-in-php.html

16 May 2019 and can customize to fit my needs. This PHP function accepts as parameter a SQL date (or date format that strtotime() can convert) and outputs the largest unit (for example "1 year" not "1 year 2 months 3 days, etc..."). function getTimeAgo($p_Date) {...

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Zoho Deluge: Convert Xero Date (Unix Timestamp) to Standard Date Stringhttps://joellipman.com/articles/crm/zoho/zoho-deluge-convert-xero-date-unix-timestamp-to-standard-date-string.html

04 May 2020 = v_MyDate.unixEpoch(); info v_UnixSeconds; Error: Argument type mismatch -Found 'TEXT' but Expected 'Long' // Solution: Convert your string into a date. Source(s) toTime() function | Help - Zoho Deluge unixEpoch() function | Help - Zoho Deluge

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

Convert seconds to total time in PHPhttps://joellipman.com/articles/web-development/php/convert-seconds-to-total-time-in-php.html

31 January 2012 to do the date/time calculations, there are times when we have to do with PHP. This is the shortest way I know to properly convert seconds into total hours, minutes and seconds (taking into account regional settings and without using a date function)....

• Type: Article
• Author: Joel Lipman
• Category: Personal Home Page
• Language: en-GB

Convert Decimal (Person Days) to Time in Excelhttps://joellipman.com/articles/microsoft/excel/convert-decimal-person-days-to-time-in-excel.html

13 February 2013 day being 7 hours and 24 minutes or 26640 seconds). How? The answer was always going to be a convoluted formula just to convert it into minutes and then format the resulting value into a custom format of [h]:mm:ss -- Aim / Objective: ColumnA ColumnB...

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

Zoho Deluge: Convert Map to URL Parametershttps://joellipman.com/articles/crm/zoho/zoho-deluge-convert-map-to-url-parameters.html

09 March 2020 A quick article on converting a Map (associative array with keys and values) into a URL string to pass as URL parameters. How? Let's first define a map: m_Payload = Map(); m_Payload.put("client_id","my-unique-client-id");...

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