Help with Date and Time Query 1

[mystuff777]

I read the FAQ on this subject, but am still doing something wrong. I have two columns in a table that are of the data type datetime. The following query should return 17 rows but instead is returning 0. What am I doing wrong?

select EngagementNumber, StartDate, convert (varchar,StartTime,108)
from Engagements
Where 
startdate between '1999-08-01' and '1999-08-31'
And StartTime between '12:00:00' and '17:00:00'
order by EngagementNumber

 

[philhege]

What date is in the StartTime column? SQL assumes the empty date (1900-01-01) if you don't specify it in your query.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[mystuff777]

Hi Phil,

Oddly enough the date in the StartTime Column for all rows is 1899-12-30. This is a sample database I am working with, so I didn't create it. So is the context of my SQL statement correct and the dates in my StartTime Column simply wrong?

 

[ESquared]

I think 1899-12-30 is the base datetime value for Office applications. 1901-01-01 is the one for SQL Server. So the data was probably inserted by Excel or Access.

So let's take this apart:

>>>startdate between '1999-08-01' and '1999-08-31'
first of all, BETWEEN is inclusive. So this means you are including 1999-08-31. Now, you've torn apart dates and times (which I don't recommend, precisely for what you're demonstrating today) so that MAY be okay. But do you have the proper constraints on this column to ensure there's never any time attached?

ALTER TABLE MyTable ADD CONSTRAINT CK_MyTable_StartDate_HasNoTime CHECK (DateDiff(dd, '0, StartDate) = '1899-12-30')

If you get an error on that, it means some of your dates have time values which could cause problems. You should do something similar for time values:

>>And StartTime between '12:00:00' and '17:00:00'

ALTER TABLE MyTable ADD CONSTRAINT CK_MyTable_StartTime_HasNoDate CHECK (DateDiff(dd, '1899-12-30', StartTime) = 0) -- assuming '1899-12-30' is correct

Again, if you get an error, this will tell you what the problem is.

In SQL 2008 there will be Date and Time data types at which time I can begin to recommend splitting those two out. In the meantime, if at all possible, I would combine them:

ALTER TABLE MyTable ADD StartDateTime NULL
UPDATE MyTable SET StartDateTime = DateDiff(dd, 0, StartTime) + StartTime - DateDiff(dd, '1899-12-30', StartTime)
ALTER TABLE MyTable ALTER COLUMN StartDateTime NOT NULL
ALTER TABLE MyTable DROP COLUMN StartDate
ALTER TABLE MyTable DROP COLUMN StartTime

Now create a view to display the StartDate and StartTime if you must,

CREATE VIEW MyTableWithDateAndTime
AS
SELECT
   ColumnList,
   StartDateTime,
   StartDate = Convert(datetime, DateDiff(dd, 0, StartDateTime)),
   StartTime = StartDateTime - DateDiff(dd, '1899-12-30', StartDateTime)

Or if you really really have to, put it in the table:

ALTER TABLE MyTable ADD StartDate AS DateAdd(dd, DateDiff(dd, 0, StartDateTime), 0)
ALTER TABLE MyTable ADD StartTime AS StartDateTime - DateDiff(dd, '1899-12-30', StartDateTime)

And again if you really really have to, you can make those values get calculated on insert/update and be materialized, instead of each select:

CREATE NONCLUSTERED INDEX IX_MyTable_StartDate On MyTable (StartDate ASC, StartTime ASC)

Experiments are in order to find out if you should split this into two indexes or make a second index with the columns reversed.

The view is better than the computed columns according to some SQL Professionals. Others have no problem with the computed columns. The view can be a materialized view, too, although that can get really messy (and is space and insert/update performance dragging) and prevents you from altering your table in any way without dropping the view.

Personally I think the best solution is to just use the datetime column:

select EngagementNumber, StartDate, convert (varchar,StartTime,108)
from Engagements
Where 
StartDateTime >= '1999-08-01' and startdate < '1999-09-01'
And StartDateTime - DateDiff(dd, '1899-12-30', StartDateTime) between '12:00:00' and '17:00:00'
order by EngagementNumber

Note: are you sure that you want '17:00:00' and not '17:59:59.997' which is more easily expressed as < '18:00:00' (or something similar, not necessarily a whole hour)?

In any case, if times should have zero minutes or seconds or should be increments of 15 or whatever, then you should have a constraint again (probably should combine intelligently in one constraint with any others on this column)

ALTER TABLE MyTable ADD CONSTRAINT CK_MyTable_StartDateTime_ValidTime (DateDiff(mi, DateDiff(dd, 0, StartDateTime), StartDateTime) % 15 = 0) -- only 15 minute increments. And is "mi" minutes? I forget.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[mystuff777]

Wow, that has to be one of the most complete answers to a post I have ever gotten. Thanks much for the great information. I am working through a book, teaching myself SQL. The book teaches SQL in general and is not specifically geared toward SQL Server, thus the seperate columns for start date and start time.

I agree that for SQL server using one column for both is better. I had no idea how to do that though until now. Thanks again.

 

[ESquared]

By the way, I have an error in there, probably more than one. But here's one:

CHECK (DateDiff(dd, '0, StartDate) = '1899-12-30')

This needs a convert datetime on one side or the other (or both):

DateDiff(dd, '0, StartDate) = Convert(datetime, '1899-12-30')

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[philhege]

Ah, I remember 1899. That was the day they invented FoxPro.