DateDiff problem ?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DateDiff problem ??

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-04 : 11:53:56

I modified this query to get me the ApptTime (DateDiff between ApptStart and ApptStop). When I run this query in QA, I get 1900-01-01. Am I missing something?

/* Provider Appointment Summary */

SET NOCOUNT ON

CREATE TABLE #Appts
(
Resource varchar(150),
Type varchar(75),
Duration int,
ApptStart datetime,
ApptStop DATETIME,
ApptTime DATETIME,
Facility varchar(150),
Cancelled bit,
Available bit
)

-- First gather appointments made (and cancelled). Both Resource and Patient.

INSERT INTO #Appts

SELECT r.ListName AS Resource, ISNULL(aa.Type,'No Appointment Type') AS Type, apt.Duration, aa.ApptStart,
aa.ApptStop, DATEDIFF(mm, aa.apptstart, aa.apptstop)AS ApptTime, f.ListName AS Facility, aa.Canceled, 0

FROM Appointments aa
INNER JOIN ApptType apt ON aa.ApptTypeID = apt.ApptTypeID
INNER JOIN DoctorFacility r ON aa.ResourceID = r.DoctorFacilityID
INNER JOIN DoctorFacility f ON aa.FacilityID = f.DoctorFacilityID

WHERE aa.ApptStart >= ISNULL('07/14/2006','1/1/1900') AND aa.ApptStart < dateadd(day,1,ISNULL('07/14/2006','1/1/3000'))
AND
--Filter on facility
(
(NULL IS NOT NULL AND aa.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Resource
(
('389' IS NOT NULL AND aa.ResourceID IN (389)) OR
('389' IS NULL)
)

-- Next get the available times, slots with no appointment ID

INSERT INTO #Appts

SELECT r.ListName, ISNULL(atype.Name,'No Appointment Allocation'), DATEDIFF(n,apptslot.Start, apptslot.Stop),
apptslot.Start, apptslot.Stop, '', f.ListName,0,1

FROM ApptSlot apptslot
INNER JOIN Schedule s ON apptslot.ScheduleID = s.ScheduleID
INNER JOIN DoctorFacility f ON apptslot.FacilityID = f.DoctorFacilityID
INNER JOIN DoctorFacility r ON s.DoctorResourceID = r.DoctorFacilityID
LEFT JOIN AppointmentsAlloc aa ON apptslot.ApptSlotID = aa.ApptSlotID
LEFT JOIN ApptType atype ON aa.ApptTypeID = atype.ApptTypeID

WHERE apptslot.ApptID IS NULL AND
apptslot.Start >= ISNULL('07/14/2006','1/1/1900') AND apptSlot.Start < dateadd(day,1,ISNULL('07/14/2006','1/1/3000'))
AND
--Filter on facility
(
(NULL IS NOT NULL AND apptslot.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Resource
(
('389' IS NOT NULL AND s.DoctorResourceID IN (389)) OR
('389' IS NULL)
)

SELECT * FROM #Appts
DROP TABLE #Appts

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-04 : 12:20:09

Remember that all times have a date component as well. The "base" date and time is "1/1/1900 12:00:00 AM"; this is what you get if you take a numeric value of 0 and convert it to a DateTime. Thus, you can add "1/1/1900 1:00 AM" to any dateTime value, and you are adding 1 hour to it. By the same token, if you subtract "1/1/2003 2:00PM" from "1/1/2003 3:00PM" (which is 1 hour later) you will get "1/1/1900 1:00AM" -- which is 1 hour after the "base" date and time.

So, if "1/1/1900 12:00:00 AM" is being returned when you subtract two dates, it means there is no difference between the two dates, they are equal.

- Jeff
http://weblogs.sqlteam.com/JeffS

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-04 : 12:23:11

How can Datedif return '1900-01-01'? Can you post some sample data you have thats giving this result?

************************
Life is short. Enjoy it.
************************

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 12:24:16

quote:
WHERE aa.ApptStart >= ISNULL('07/14/2006','1/1/1900') AND aa.ApptStart < dateadd(day,1,ISNULL('07/14/2006','1/1/3000'))

The date 07/14/2006 will never be NULL. Why use ISNULL function here?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 12:25:57

And the DATEDIFF value for 07/14/2006 and 07/14/2006 is exactly zero days.
Now read Jeffs post very carefully.

Peter Larsson
Helsingborg, Sweden

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-04 : 12:28:55

Peso,

This is how the code would look in design mode (This is prior to the SQL changes I made in my first post).

/* Provider Appointment Summary */

SET NOCOUNT ON

CREATE TABLE #Appts (
Resource varchar(75),
Type varchar(75),
Duration int,
ApptStart datetime,
Facility varchar(75),
Cancelled bit,
Available bit
)

-- First gather appointments made (and cancelled). Both Resource and Patient.

INSERT INTO #Appts
SELECT r.ListName AS Resource, ISNULL(aa.Type,'No Appointment Type') AS Type, apt.Duration, aa.ApptStart, f.ListName AS Facility, aa.Canceled, 0
FROM Appointments aa INNER JOIN
ApptType apt ON aa.ApptTypeID = apt.ApptTypeID INNER JOIN
DoctorFacility r ON aa.ResourceID = r.DoctorFacilityID INNER JOIN
DoctorFacility f ON aa.FacilityID = f.DoctorFacilityID
WHERE aa.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND aa.ApptStart < dateadd(day,1,ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
AND
--Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND aa.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND
--Filter on Resource
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND aa.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)

-- Next get the available times, slots with no appointment ID

INSERT INTO #Appts
SELECT r.ListName, ISNULL(atype.Name,'No Appointment Allocation'), DATEDIFF(n,apptslot.Start, apptslot.Stop), apptslot.Start, f.ListName,0,1
FROM ApptSlot apptslot INNER JOIN
Schedule s ON apptslot.ScheduleID = s.ScheduleID INNER JOIN
DoctorFacility f ON apptslot.FacilityID = f.DoctorFacilityID INNER JOIN
DoctorFacility r ON s.DoctorResourceID = r.DoctorFacilityID LEFT JOIN
AppointmentsAlloc aa ON apptslot.ApptSlotID = aa.ApptSlotID LEFT JOIN
ApptType atype ON aa.ApptTypeID = atype.ApptTypeID
WHERE apptslot.ApptID IS NULL AND
apptslot.Start >= ISNULL(?DATERANGE.DATE1?,'1/1/1900') AND apptSlot.Start < dateadd(day,1,ISNULL(?DATERANGE.DATE2?,'1/1/3000'))
AND
--Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND apptslot.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND
--Filter on Resource
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND s.DoctorResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)

SELECT * FROM #Appts
DROP TABLE #Appts

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-04 : 12:32:51

Dinakar,

This is what I'm getting back as a sample for one appointment: (The time is not converting to 15 min as I'd like)

ApptStart: 2006-07-14 09:15:00.000
ApptStop: 2006-07-14 09:30:00.000
ApptTime: 1900-01-01 00:00:00.000

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-04 : 13:06:10

What are the values for which you are getting the value "ApptTime: 1900-01-01 00:00:00.000"
Also you are doing a datediff for "mm" which means SQL Server will return difference only in months. so anything < 1 month will be returned as 0.

************************
Life is short. Enjoy it.
************************

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-04 : 13:15:28

dinakar,

oh my --- I didnt notice I had "mm" -- I changed to "mi" and now get this:

ApptStart: 2006-07-14 09:15:00.000
ApptStop: 2006-07-14 09:30:00.000
ApptTime: 1900-01-16 00:00:00.000

How can I get it to read as 15 minutes?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-04 : 13:54:05

select datediff(mi, '2006-07-14 09:15:00.000', '2006-07-14 09:30:00.000')

your result should be 15. so you can do DateDiff(mi,AppSatrt, AppStop)

************************
Life is short. Enjoy it.
************************

JeffS23
Posting Yak Master

212 Posts

Posted - 2007-04-04 : 14:16:06

dinakar,

I have multiple appointments ... how can I code this with my entire query to return minutes?

So adding your logic to this query:

/* Provider Appointment Summary */

SET NOCOUNT ON

CREATE TABLE #Appts
(
Resource varchar(150),
Type varchar(75),
Duration int,
ApptStart datetime,
ApptStop DATETIME,
ApptTime DATETIME,
Facility varchar(150),
Cancelled bit,
Available bit
)

-- First gather appointments made (and cancelled). Both Resource and Patient.

INSERT INTO #Appts

SELECT r.ListName AS Resource, ISNULL(aa.Type,'No Appointment Type') AS Type, apt.Duration, aa.ApptStart,
aa.ApptStop, DATEDIFF(mi, aa.apptstart, aa.apptstop)AS ApptTime, f.ListName AS Facility, aa.Canceled, 0

FROM Appointments aa
INNER JOIN ApptType apt ON aa.ApptTypeID = apt.ApptTypeID
INNER JOIN DoctorFacility r ON aa.ResourceID = r.DoctorFacilityID
INNER JOIN DoctorFacility f ON aa.FacilityID = f.DoctorFacilityID

WHERE aa.ApptStart >= ISNULL('07/14/2006','1/1/1900') AND aa.ApptStart < dateadd(day,1,ISNULL('07/14/2006','1/1/3000'))
AND
--Filter on facility
(
(NULL IS NOT NULL AND aa.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Resource
(
('389' IS NOT NULL AND aa.ResourceID IN (389)) OR
('389' IS NULL)
)

-- Next get the available times, slots with no appointment ID

INSERT INTO #Appts

SELECT r.ListName, ISNULL(atype.Name,'No Appointment Allocation'), DATEDIFF(n,apptslot.Start, apptslot.Stop),
apptslot.Start, apptslot.Stop, '', f.ListName,0,1

FROM ApptSlot apptslot
INNER JOIN Schedule s ON apptslot.ScheduleID = s.ScheduleID
INNER JOIN DoctorFacility f ON apptslot.FacilityID = f.DoctorFacilityID
INNER JOIN DoctorFacility r ON s.DoctorResourceID = r.DoctorFacilityID
LEFT JOIN AppointmentsAlloc aa ON apptslot.ApptSlotID = aa.ApptSlotID
LEFT JOIN ApptType atype ON aa.ApptTypeID = atype.ApptTypeID

WHERE apptslot.ApptID IS NULL AND
apptslot.Start >= ISNULL('07/14/2006','1/1/1900') AND apptSlot.Start < dateadd(day,1,ISNULL('07/14/2006','1/1/3000'))
AND
--Filter on facility
(
(NULL IS NOT NULL AND apptslot.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Resource
(
('389' IS NOT NULL AND s.DoctorResourceID IN (389)) OR
('389' IS NULL)
)

SELECT * FROM #Appts
DROP TABLE #Appts

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-04 : 14:40:53

Your Datediff part of the code looks fine.
I have no idea what you are trying to do here:

--Filter on facility
(
(NULL IS NOT NULL AND aa.FacilityID IN (NULL)) OR
(NULL IS NULL)
)
AND
--Filter on Resource
(
('389' IS NOT NULL AND aa.ResourceID IN (389)) OR
('389' IS NULL)
)

Also, as others have mentioned, ISNULL('07/14/2006','1/1/1900') is absurd.

************************
Life is short. Enjoy it.
************************

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-04 : 14:45:19

See these
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81191
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80222

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources