Tuesday, July 29, 2003

SQL: Date Searching

Just some useful tips for searching on date fields (Microsoft SQL Server 2000+):

You can use the LIKE operator to do a search:

SELECT * FROM [tablename] WHERE [datecolumn] LIKE '%Aug%2003%'

This would match everything that had August 2003 in datecolumn e.g. August 12 2003, August 18 2003

The other way is to use DATEPART as follows:

SELECT * FROM [tablename] WHERE DATEPART(month,[datecolumn]) = 8 AND DATEPART(year,[datecolumn]) = 2003

The final way is to use MONTH and YEAR:

SELECT * FROM [tablename] WHERE MONTH([datecolumn]) = 8 AND YEAR([datecolumn]) = 2003

N.B. If you use DATEPART, MONTH, or YEAR, the supplied value has to be numeric (8 instead of August)

Monday, July 28, 2003

Javascript: Remove Items from Array

I required some javascript for removing an item (or series of items) from an array. Thankfully, I found out how to do it from this thread in Google Groups.

e.g. (paste into HTML document):
<script language="javascript">
// this is the original array
var theArray = [17,2,5,675];
// these are the items I wish to remove from the array
var toRemove = [17,2];
// it can be done with one item only as well
// var toRemove = 5;

// adding it as a prototype object enables it to be used from any array
Array.prototype.removeItems = function(itemsToRemove) {

    if (!/Array/.test(itemsToRemove.constructor)) {
        itemsToRemove = [ itemsToRemove ];

    var j;
    for (var i = 0; i < itemsToRemove.length; i++) {
        j = 0;
        while (j < this.length) {
            if (this[j] == itemsToRemove[i]) {
                this.splice(j, 1);
            } else {

Thanks to Grant Wagner for this. It also can be applied to server-side ASP pages as well as client-side HTML (after setting JavaScript as the page language). However, there seems to be something wrong with an array of form elements (they are comma-space seperated i.e. "2, 5, 75, 120"), so it has to be split using var arItems = String(Request.Form("items")).split(", ")

NB. Array.splice is used, but is not supported by all browsers.

There is also a method of doing it without using splice (thanks to Dr John Stockton for this) in this thread. I have not implemented it yet - might use it as client side code.

var Delenda = [], j, n = 0, T, U // Undefined value

  for (j = 0 ; j < toRemove.length ; j++ )
    Delenda[toRemove[j]] = true                         // or 0 !

  for (j = 0 ; j < theArray.length ; j++ )     
    if ( Delenda[ T = theArray[j] ] === U ) theNewArray[n++] = T

Finally, there is a method using Regular Expressions (thanks go to Greg for this):
<script type='text/javascript'>
function removeVal(arr, valToRemove){
  // Normalize to a string like !val!!val!!val!
  var s = '!' + arr.join('!!') + '!';
  // Remove targeted values with delimiters
  s = s.replace(new RegExp('!' + valToRemove + '!', 'g'), '');
  // Remove delimiter added to end in step 1
  s = s.replace(/^!/, '');
  // Remove delimiter added to start in step 1
  s = s.replace(/!$/, '');
  // Convert to array
  return s.split('!!');

function test(){
  var arr = [2,1,2,2,5,7,12,15,21,2]; // Check case with end vals
  var s = arr.toString();
  arr = removeVal(arr, 2)
  alert(s + '\n' + arr.toString());

<button onclick='test()' />test()</button>

Friday, July 25, 2003

First Weblog - Toolbars

This weblog was set up using Google Toolbar BETA 2.0. Best feature of this is probably the popup blocker (no need for installing standalone software for doing the same thing, IE 5.5 and up only).

Some other useful toolbars: The Dictionary.com Toolbar is also very good - online dictionary and thesaurus search.

DevelopersDex Toolbar for developers in ASP, C#, SQL, VB and XML. Lots of examples, articles and tutorials are available in the DevelopersDex Site as well as the possibility to post to USENET (Newsgroups).

The toolbars for Internet Explorer only