Date range

What is the best way to do a where clause that includes a date range. Ex. WHERE date1 BETWEEN @.Begin Date AND @.EndDate. I want to include all of the @.EndDate.well, BETWEEN is inclusive but your probably not receiving all of the enddates due to the time part of datetime datatype.

Another method is to add 1 day to the enddate (with no time indicator) and use:

WHERE date1 >= @.beginDate AND date1 < @.enddate

|||I used WHERE date1 BETWEEN @.Begin Date AND @.EndDate + '24:59:59' to include the data from the last day of the search.

So I assume there is no benefit from using between to < and >.|||Your query is not correct. It will exclude 1 second before midnight (SQL Server datetimes do include ms)

You should be using:

where Date1 >= @.Begin
 and Date1 < @.End + 1

OR

where convert(datetime, convert(varchar(10), Date1, 111), 111) = @.Begin

if it's only one day|||Using the CONVERT function would make the query non-sargable -- don't do it that way!! Definitely stick with Pierre's first option.

Terri|||Adding one to the date doesn't pose any issues? @.date + 1?|||As long as @.date does not contain any time data (ie it is the default midnight 00:00:00.000) that method should be fine.

Terri

Date field cannot be null!

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

With this it looks like your table design has the field set not to allow nulls. You will have to alter the table definition to allow nulls for that field. This can be done in raw tsql, or using the table design views in either of the management studio tools.

|||

I went into DataSetDesigner and in Properties I set AllowDbNull to True.

But, when I tried to change the NullValue property from "Throw Exception" to "Empty" or "Nothing" (there are only 3 choices) it said "For columns not defined as System.String, the only valid value is (Throw exception)".

|||

Given that you've just allowed nulls, the value of the NullValue property is irrelevant, as the exception should never be raised.

|||

The following exception occurred in the DataGridView:

System.Data.NoNullAllowedException: Column 'BirthDate' does not allow nulls

I set 'AllowDbNull' to True in the properties field of the DataSet Designer, replied 'Yes' to 'Save Changes', but, when I go back in, 'AllowDbNull' is back to False.

|||

ok - I've done some research on this (I'm a SQL guy, not a VS guy) and it looks like there may be a bug in the Dataset Designer for non-string columns. I found what looks like a workaround here: http://www.codeproject.com/useritems/Bug_fixed_in_DataDesigner.asp

Hope this helps