Difference between two dates - the midnight hourhttps://joellipman.com/articles/web-development/difference-between-two-dates-the-midnight-hour.html

06 April 2011 $sum_hours = number_format(((strtotime($this_time_from_sql)-strtotime($this_time_from_sql))/60)/60, 2); // Using the examples above this is doing the following: 2011-03-04 12:00:00 - 2011-03-04 10:00:00 = 2.00 2011-03-08 00:30:00 - 2011-03-08 23:30:00 =...

• Type: Article
• Author: Joel Lipman
• Category: Web-Development
• Language: *

Creating a Profile Plugin for Joomla 1.6https://joellipman.com/articles/cms/joomla/creating-a-profile-plugin-for-joomla-16.html

27 April 2011 all this for me... Neat huh? Folder I'm going to shove all these files into a folder which I will then compress and install using the Joomla! CMS Extension Manager. I'm going to call the folder "profile5" (without the double-quotes) and create a...

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

Could not instantiate mail functionhttps://joellipman.com/articles/cms/joomla/could-not-instantiate-mail-function.html

09 May 2011 domain.com and you want to recieve mail on user@domain.com, you must *not* set your hostname to domain.com. We recommend using server.domain.com instead. You must make sure that you add the A record for server.domain.com so that it resolves. 2) The...

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

Sort order a dropdown list in MS InfoPath without programminghttps://joellipman.com/articles/microsoft/infopath/sort-order-a-dropdown-list-in-ms-infopath-without-programming.html

21 July 2011 I entered them in the datasheet. Googling this led me to browse MSDN for an hour before I realised all those experts were using programming solutions that seemed a bit over the top for something that should be so simple. Hey presto, I found a...

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

SSRS / MDX Date Querieshttps://joellipman.com/articles/microsoft/ssrs/ssrs-mdx-date-queries.html

03 August 2011 I find myself using date ranges endlessly as I've been working in SSRS. The below is derived from a collection of various sources across the web as well as some of my own. These are what worked in my environment: WinXP, BIDS (vs2008), TFS (vs2010), SSRS...

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

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

08 August 2011 columns (which measure in milliseconds) and convert to a time value with milliseconds showing. Now why don't these match? Using in an aggregate, how do I get the milliseconds between start and end times instead of summing the remaining columns?...

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

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

24 August 2011 What's the problem? Enter this as a query in Excel without the parameters and it will work. Set MySQL parameters (using @ - same as T-SQL) and Microsoft Excel 2007 gets confused. Putting it all together Create a SQL query that will return: staffname,...

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

SSRS Parameters in Oracle Stored Procedurehttps://joellipman.com/articles/microsoft/ssrs/ssrs-parameters-in-oracle-stored-procedure.html

02 September 2011 character ORA-06512: at "SYS.DBMS_UTILITY", line 114 ORA-06512: at line 1 (System.Data.OracleClient) The Situation I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to...

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

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

30 September 2011 Situation I have a Microsoft Excel 2007 file that connects to a SQL Server 2008 R2 database. The Excel file pulls data using lookup tables and displays the data in an Excel Spreadsheet. The Problem We can select all cells and set row height to be 30 for...

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

SSRS Retrieving Oracle Stored Procedure Success or ErrorLevelhttps://joellipman.com/articles/microsoft/ssrs/ssrs-retrieving-oracle-stored-procedure-success-or-errorlevel.html

04 November 2011 the one to execute the stored procedure, we use an account specifically created to run the stored procedure. This requires using 2 data sources in SSRS. A user will specify the student reference in the parameters of the first report (the one with all...

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

SSRS AlphaNumeric Parameter Validationhttps://joellipman.com/articles/microsoft/ssrs/ssrs-alphanumeric-validation.html

07 November 2011 is alphanumeric, 0 if not alpha numeric. SELECT @ParameterToCheck REGEXP '^[A-Za-z0-9]+$' AS AlphaNumeric FROM... Usage -- Using the ORACLE example SELECT Column1, Column2, (LENGTH( TRIM( TRANSLATE( :ParameterToCheck,...

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

SSRS Zero Paddinghttps://joellipman.com/articles/microsoft/ssrs/ssrs-zero-padding.html

10 November 2011 modifying the SQL query. The zero padding would need to be applicable within an MDX query. The Situation We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log...

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

MySQL: Display Users and Duration in Matrix Timesheethttps://joellipman.com/articles/database/mysql/mysql-display-users-and-duration-in-matrix-timesheet.html

27 April 2012 ...do some more here ???... } } Yields -- Note this data matches the brief as we've converted the seconds to time. -- Using MySQL to convert rather than PHP so as not to have to deal with hour changes and the magical 23:00 hour. -- 2nd Note: We use...

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

Print Directory Contents to a Filehttps://joellipman.com/articles/microsoft/windows-os/print-directory-contents-to-a-file.html

19 July 2012 we add the "Command prompt from here" option (now built-in to Windows 7). I added this option for him in Windows 7 Ultimate using the system registry (see "How: for Windows 7" below). -- yield a_subfolder_in_this_folder a_file_in_this_folder.txt...

• Type: Article
• Author: Joel Lipman
• Category: Windows OS
• Language: *

Connect to Joomla database in standalone scripthttps://joellipman.com/articles/cms/joomla/connect-to-joomla-database-in-standalone-script.html

17 August 2012 details and extract data from the database. How? Based on a script I found on StackOverflow. Not sure what version they were using so I modified it to make this work with my environment (Joomla 2.5.6). This is a cut down version of my download.php...

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

Basic Joomla 2.5 Mootools Formhttps://joellipman.com/articles/cms/joomla/basic-joomla-25-mootools-form.html

20 August 2012 who just want to see an example of an AJAX form within Joomla 1.6.x - 2.5.x in it's most basic state. At time of print, I am using this with Joomla 2.5.6. Why? The examples of the official site (mootools.net) did not work in my Joomla environment nor...

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

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

22 August 2012 the same length and the same type of data. Why? The joy of working with the plethora of applications out there result in me using MySQL, Oracle PL/SQL and Transact SQL in my day job. The equivalent functions have to be written up all in the name of...

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

T-SQL example of Case-Sensitive Soundexhttps://joellipman.com/articles/database/t-sql/t-sql-example-of-case-sensitive-soundex.html

10 October 2012 Additional Note how this also picked up typos in the 4th value which is good as I didn't want to overcomplicate things using LEVENSHTEIN and DIFFERENCE: SOUNDEX('Data Not Yet Available') = SOUNDEX('Daat oNt EYt Avialable')

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

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

26 October 2012 Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns. How? IF...

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

Search a database for a value and count matching rowshttps://joellipman.com/articles/database/search-a-database-for-a-value-and-count-matching-rows.html

05 November 2012 value. ** ** NOTE: This script can be run separate to a stored procedure by ** ** declaring @p_Value and @p_Column and using only the code ** ** from the BEGIN to the END (exclusive) - allows you to run ** ** this against any table only changing the...

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