Saturday, September 12, 2009

daterange for jqueryui

I've been using jquery a lot. I write a lot of reports, and I've not had a lot of time to add a date picker. I finally decided to do some research. I found that jqueryui (the sister to jquery) has a datepicker. What's even better is that it has a range option. Well, it did. Unfortunately that was in version 1.2 something. I really need a range. In doing some research, I found the the jqueryui team points you to a really cool plugin. Although I think it is pretty cool, I don't really think it meets my need. That being said, this was my solution, and a very simple one at that:

Required:
jquery 1.3.2
jqueryui core 1.7.2
jqueryui datepicker widget
jquery field plugin

You need to include these files, and you should probably include the jqueryui css as well, although the docs say that isn't necessary (i saw the calendar without the css, and I'd suggest using the css).

Basic input from a form:

<label for="startDate">Start Date:</label><input type="text" id="startDate" name="startDate" value="09/02/2009" autocomplete="off"><br/>
<label for="endDate">End Date:</label><input type="text" id="endDate" name="endDate" value="09/04/2009" autocomplete="off"><br/>
<div id="daysBetween"></div>

I supply a default date so that use of the fields is only required if you want to change the dates. The one peculiar thing is the empty div. This doesn't have to be included, but I want my users to know how many days are between the dates, and this is where I place that information.

The javascript:

var $endDate = $("#endDate");
var $startDate = $("#startDate");
var startDateMax = new Date($endDate.getValue());
var endDateMin = new Date($startDate.getValue());
var $daysBetween = $("#daysBetween");
var oneDay=1000*60*60*24;
$(window).load(function(){
$startDate.datepicker({changeMonth: true, changeYear: true, minDate: '-2Y', maxDate: startDateMax});
$endDate.datepicker({changeMonth: true, changeYear: true, minDate: endDateMin, maxDate: '+0D'});
calcDwell(startDateMax, endDateMin);
});
$(window).load(function(){
$startDate.change(function() {
changeDates();
});
$endDate.change(function() {
changeDates();
});
});
function changeDates(){
$(function () {
var startDateMax = new Date($endDate.getValue());
var endDateMin = new Date($startDate.getValue());
$startDate.datepicker('option', 'maxDate', startDateMax);
$endDate.datepicker('option', 'minDate', endDateMin);
calcDwell(startDateMax, endDateMin);
})
}
function calcDwell(startDateMax, endDateMin){
$daysBetween.text('Days: ' + (Math.ceil((startDateMax.getTime() - endDateMin.getTime())/(oneDay))));
}

Since I will be using the jquery selectors over, I set them to variables. I initialize the datepicker, and then wait for a change on #startDate or #endDate. If that occurs, I call changeDates, which resets the min and max dates for each field. I also recalculate the daysBetween after the change.

The getVal function comes from Dan's field plugin. I was originally just using js to get the value, but after the value changes, it no longer works. I decided to check for a better way. I came across a google thread where Dan pointed to his plugin (I'm surprised at how many times I find CFers in jquery threads). I added it, and it just worked. I plan to retrofit this into the rest of my application.

I later found a way to just use jquery, but I am happy with the way it is. I wanted to offer this post, as I did a lot of searching and found little to nothing of how to do this. Hopefully you will find this post useful.

Thursday, March 19, 2009

SQL Like Null

Wow! That is interesting...

I just ran into a situation I have never known I was in before (I know be careful where you run). I was writing some SQL (one of my favorite things to do), and had a record not show up that I knew matched the criteria. I use a like operator in the query and in the table used to compare I have a wild card (%). This works great. Except for when the main table has a null.

The reason the record didn't show up was due to the fact that the table had a null cell. Since I was working in Oracle, it was very easy to fix this.

nvl(table1.column_name, ' ') like table2.column_name


That fixed it. I really prefer MS SQL Server over Oracle, but that is a fantastic little method/function.

So I figured this wouldn't be the same in SQL Server. Sure enough like doesn't work with a null cell. I'm not taking the time now to think about how to handle this in SQL Server, but I'm sure it's much longer.

Come to think about it I suppose this would work:

(table1.column_name like table2.column_name or table1.column_name is null)


So there you have it.