Prev: Saving an image to SQL Server's image datatype
Next: Duplicate columns across 4 columns
From: mscertified on 17 Jul 2008 11:41
My tables have date ranges where the 'to' date can be open. I was advised not
to use nulls in date/time columns. How is this normally done? I was told to
put something like '12/31/2999' in there and test for that instead of null
but this seems strange to me.
From: Eric Isaacs on 17 Jul 2008 12:18
NULL typically means the value is not known or not set. In SQL, I
would suggest you use NULL values so you don't inadvertently SAVE the
invalid dates in the database. When evaluating the date, use a WHERE
clause similar to the following.

WHERE
Table1.DateTimeColumn BETWEEN(COALESCE(@BeginDateTime,
Table1.DateTimeColumn) AND COALESCE(@EndDateTime,
Table1.DateTimeColumn)

Different developers will agree or disagree on the techniques used.
It's a matter of priorities.

This would also likely be valid for most instances and would run
faster when used just for criteria:

SET @BeginDateTime = COALESCE(@BeginDateTime, '18000101')
SET @EndDateTime = COALESCE(@EndDateTime, '29991231')

....
WHERE
Table1.DateTimeColumn BETWEEN(@BeginDateTime AND @EndDateTime)


-Eric Isaacs
From: Alex Kuznetsov on 17 Jul 2008 12:19
On Jul 17, 10:41 am, mscertified <rup...(a)tigerlily.com> wrote:
> My tables have date ranges where the 'to' date can be open. I was advised not
> to use nulls in date/time columns. How is this normally done? I was told to
> put something like '12/31/2999' in there and test for that instead of null
> but this seems strange to me.

To me it looks like normal practice. Much easier to write queries
against.
From: mscertified on 17 Jul 2008 12:40
Except it will look a little odd to the user to see 'to date: 12/31/2999' on
the web page

"Alex Kuznetsov" wrote:

> On Jul 17, 10:41 am, mscertified <rup...(a)tigerlily.com> wrote:
> > My tables have date ranges where the 'to' date can be open. I was advised not
> > to use nulls in date/time columns. How is this normally done? I was told to
> > put something like '12/31/2999' in there and test for that instead of null
> > but this seems strange to me.
>
> To me it looks like normal practice. Much easier to write queries
> against.
>
From: Eric Russell on 17 Jul 2008 13:07
I think it should be determined based on what performs best given the
specific cirsumstances; don't just let the app or report developers tell you
which method to use simply based on what saves them the most keystrokes when
coding the SQL WHERE clause. Also, there is no widely accepted standard
practice either way.

I generally try to avoid using stub values and do something like this:
....
where
StartDate <= @DateOfService and
(EndDate >= @DateOfService or EndDate is null)

However, on a table with 800k rows, when I updated EndDate = '12/31/2999'
where EndDate is null and then tried the following while alternating between
the two methods, SQL Profiler indicated this 2nd method consistently consumed
about 30% less CPU and had a duration of 30% less.
....
where
@DateOfService between StartDate and EndDate

It also depends a lot on your specfific table schema, what non-key columns
you're returning, statistics, etc. so experiment too see what works best on
your end.

Also, when searching on a date range, a counpound index on StartDate and
EndDate is generally better than a seperate index on the two columns.


"mscertified" wrote:

> My tables have date ranges where the 'to' date can be open. I was advised not
> to use nulls in date/time columns. How is this normally done? I was told to
> put something like '12/31/2999' in there and test for that instead of null
> but this seems strange to me.
 |  Next  |  Last
Pages: 1 2
Prev: Saving an image to SQL Server's image datatype
Next: Duplicate columns across 4 columns