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

23 March 2011 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

Data Randomization Function in Oracle PL/SQLhttps://www.joellipman.com/articles/database/pl-sql/data-randomization-function-in-oracle-plsql.html

22 August 2012 a second parameter where you specify the data type: CREATE OR REPLACE FUNCTION UFN_DATASCRAMBLE ( p_original_value IN VARCHAR2, p_original_datatype IN VARCHAR2, p_original_range_1 IN VARCHAR2 DEFAULT NULL, -- no effect on strings p_original_range_2 IN...

• Type: Article
• Author: Joel Lipman
• Category: Oracle PL/SQL
• Language: en-GB

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

30 January 2011 AS lft, @new_jos_categories_id_last_lft+1 AS rgt, 1 AS `level`, CAST(@new_jos_categories_extension_alias AS CHAR) AS path, 'com_banners' AS extension, c.`title`, c.`alias`, CONCAT('Import from J15. PreviousID=catid', c.`id`) AS note, c.`description`,...

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

Trim in T-SQL and SSIShttps://www.joellipman.com/articles/microsoft/ssis/trim-in-t-sql-and-ssis.html

14 May 2013 carriage returns and line feeds (new lines). How? First in Transact-SQL: SELECT RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') ) -- CHAR(9) = Tab -- CHAR(10) = Line Feed -- CHAR(13) = Carriage Return And in SSIS:...

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

Stop Excel Row Height Self-Adjust on Refreshhttps://www.joellipman.com/articles/microsoft/excel/stop-excel-row-height-self-adjust-on-refresh.html

30 September 2011 JobTitle, Dept FROM my_imaginary_table WHERE Contact='Joe' Would become SELECT Date, Reservation, Contact, JobTitle, CHAR(10) + Dept + CHAR(10) AS Dept FROM my_imaginary_table WHERE Contact='Joe' Now when Excel refreshes the data, it acknowledges new...

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

T-SQL Record Separatorhttps://www.joellipman.com/articles/database/t-sql/t-sql-record-separator.html

11 October 2011 display/print but SQL*Plus has a nifty separator record activated similarly to the following: SET RECSEP WRAPPED SET RECSEPCHAR "-" Stage #1: Duplicate the table and create a separator marker Granted this will slow the query down a touch and only adds a...

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

Before and After, Highs and Lowshttps://www.joellipman.com/articles/microsoft/ssrs/before-and-after-highs-and-lows.html

03 August 2011 DATEPART(week, ExecutionLogStorage.TimeStart), DATEPART(year, ExecutionLogStorage.TimeStart), Catalog.Name Chart-wise: create a line chart [Sum(Counter)] will be the data field, [ReportName] will be the series field and [DayOfYearRun] will be category...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server Reporting Services
• Language: *

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 is expanded in a further article called "Search a database with soundex": /* USING A CURSOR */ DECLARE @SqlToExecute nvarchar(max); DECLARE @mySearchString varchar(50); SET @mySearchString = 'dnya'; DECLARE MyCursor CURSOR FOR SELECT 'SELECT ' +...

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

Win32 Constantshttps://www.joellipman.com/articles/automation/autohotkey/win32-constants.html

05 July 2011 Const WM_DRAWITEM = $002B Const WM_MEASUREITEM = $002C Const WM_DELETEITEM = $002D Const WM_VKEYTOITEM = $002E Const WM_CHARTOITEM = $002F Const WM_SETFONT = $0030 Const WM_GETFONT = $0031 Const WM_SETHOTKEY = $0032 Const WM_GETHOTKEY = $0033 Const...

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

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

08 August 2011 serious lack of documentation as to what this database is and how it populates its data. View: ExecutionLog InstanceName nvarchar(38) NOT NULL Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER ReportID...

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

How to Display Report Execution Time in SSRS (milliseconds)https://www.joellipman.com/articles/microsoft/ssrs/how-to-display-report-execution-time-in-ssrs.html

17 November 2011 -- Oracle 10g: DataSet1 -- your normal dataset query with an added field (ReportStartTime) SELECT field1, field2, TO_CHAR(systimestamp, 'HH24:MI:SS.FF6') AS ReportStartTime FROM... Then add another dataset (using the same data source if you like): --...

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

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

01 August 2012 a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling. Why? We...

• 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 which also help scramble database tables sent to suppliers/developers, this is a function which simply finds random characters and inserts these. I would recommend using the DataTumble script over this one as this leaves data very difficult to work...

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

Include a carriage return in a column headinghttps://www.joellipman.com/articles/microsoft/sql-server/include-a-carriage-return-in-a-column-heading.html

07 July 2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014... How? To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the...

• Type: Article
• Author: Joel Lipman
• Category: SQL Server
• Language: *

Modifying columns in a tablehttps://www.joellipman.com/articles/database/modifying-columns-in-a-table.html

24 July 2013 poorly programmed when even I've made better DBMS tools). All SQL -- Add a column to an existing table (giving it datatype char(2) and allowing NULL) ALTER TABLE myTable ADD myColumn CHAR(2) NULL -- Delete a column ALTER TABLE myTable DROP COLUMN...

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

MySQL Data Type Reference Tablehttps://www.joellipman.com/articles/database/mysql/mysql-data-type-reference-table.html

15 February 2010 8 bytes Maximum non-zero values: 1.7976931348623157E+308 Numeric Decimal Varies Maximum width + 2 bytes String Char Range 1-255 characters Always filled max width String VarChar Range 1-255 characters Length of string + 1 byte String TinyBlob, TinyText...

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

Alternative method of displaying Unicode in Autohotkey GUIhttps://www.joellipman.com/articles/automation/autohotkey/alternative-method-of-displaying-unicode-in-autohotkey-gui.html

29 September 2011 Generate list of those that work for you: loop 255 fileappend,% a_index . A_Tab . A_Space . Chr(a_index) . "`n",ascii-chartable.txt A bunch of others that may work: Some others that may work: Dec Hex Unicode Char Description ------- ------- -------...

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

Returning Oracle Stored Procedure Resultset in SSRShttps://www.joellipman.com/articles/microsoft/ssrs/returning-oracle-stored-procedure-resultset-in-ssrs.html

19 January 2012 stored procedure using a cursor for SSRS CREATE OR REPLACE PROCEDURE sp_get_studentdetails_from_ad ( p_STUDENT_ADNAME IN varchar2, l_CURSOR OUT sys_refcursor ) IS BEGIN -- going to declare variables that are to be returned in SSRS but not -- mentioned...

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

Cheat Sheet for mySQL vs t-SQLhttps://www.joellipman.com/articles/database/cheat-sheet-for-mysql-vs-t-sql.html

31 January 2013 String Position INSTR(haystack, needle) LOCATE(needle, haystack [, offset]) PATINDEX(%needle%, haystack) CHARINDEX(needle, haystack [, offset]) String Concatenation CONCAT(string1,string2[,stringN]) string1 + string2 SS v2012+: CONCAT(string1, string2...

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

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 just converts a string of words delimited by spaces to a table: CREATE FUNCTION dbo.[ufn_StringToTable] ( @StringInput VARCHAR(MAX) ) RETURNS @OutputTable TABLE ( StringValue VARCHAR(10) ) AS...

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