Need some help on date/time comparison

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

SQL Server Development (2000)

Need some help on date/time comparison

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2003-06-16 : 03:18:27

I Have two tables with date/time fields.
I need to compare the date from one with the date in the other.

this is a piece of the code...

/**************************************/
DECLARE @Planned as varchar(1),
@StartDate as datetime,
@EindDate as datetime,
@SkepCode as varchar(2),
@Lne as varchar(2),
@Reason as integer,
@Type varchar(3),
@To_Item varchar(6),
@From_Item varchar(6),
@Maintenance as integer
BEGIN
-- modified on 24/07/2002 : added the PPS type
-- Get the inserted values into local variables to work with
SELECT @StartDate = STARTDATUMTIJD, @EindDate = EINDDATUMTIJD,
@SkepCode = LNE, @Reason = ID_SUBREDEN, @From_Item = PREVIOUSITEM, @To_Item = ITEM FROM INSERTED
-- Convert Skepcode to Line number
SELECT @Lne = CODE FROM INFO_LIJN WHERE SKEPCODE = @SkepCode
-- Next get the value for ISType
-- default ISType and Planned
SET @Type = 'OBW'
SET @Planned = 'N'
-- Check to see if startdate or enddate in a planned maintenance period falls
SELECT @Maintenance = COUNT(*) FROM info_planned_maintenance
WHERE LineCode = @SkepCode AND MAINTENANCE = 'Y'
AND (( @StartDate BETWEEN STARTDATE AND ENDDATE) OR ( @EindDate BETWEEN STARTDATE AND ENDDATE))
-- If planned
IF @Maintenance >= 1
BEGIN
SET @Type= 'MTE'
SET @Planned = 'Y'
END
-- Check to see if startdate or enddate in a planned stop period falls
SELECT @Maintenance = COUNT(*) FROM info_planned_maintenance
WHERE LineCode = @SkepCode AND MAINTENANCE = 'N'
AND (( @StartDate BETWEEN STARTDATE AND ENDDATE) OR ( @EindDate BETWEEN STARTDATE AND ENDDATE))
-- If planned
IF @Maintenance >= 1
BEGIN
SET @Type= 'PPS'
SET @Planned = 'Y'
END
.
.
.
.
.
.
.
/************************************************/

But now they compare the whole datetime, while I would like to compare only the date and the hour and the minutes.
Not the seconds and the milliseconds...

something like this ..if I would replace the var by the realtime data
where STARTDATE/ ENDATE would look like

.
.
row X - 2003-06-06 14:00:00.000 -- 2003-06-06 22:00:00.000
row X+1 - 2003-06-06 22:00:00.000 -- 2003-06-07 06:00:00.000
row X+2 - 2003-06-07 06:00:00.000 -- 2003-06-07 14:00:00.000
row X+3 - 2003-06-07 14:00:00.000 -- 2003-06-07 22:00:00.000
row X+4 - 2003-06-07 22:00:00.000 -- 2003-06-08 06:00:00.000
row X+5 - 2003-06-08 06:00:00.000 -- 2003-06-08 14:00:00.000
.
.

SELECT @Maintenance = COUNT(*) FROM info_planned_maintenance
WHERE LineCode = @SkepCode AND MAINTENANCE = 'Y'
AND (( '2003-06-06 16:42' BETWEEN STARTDATE AND ENDDATE))
OR ( '2003-06-06 17:02' BETWEEN STARTDATE AND ENDDATE))

Also what happens if the @StartDate = '2003-06-06 14:00:03.000' and I repeat the select from here above..

SELECT @Maintenance = COUNT(*) FROM info_planned_maintenance
WHERE LineCode = @SkepCode AND MAINTENANCE = 'Y'
AND (( '2003-06-07 14:00:03.000' BETWEEN STARTDATE AND ENDDATE)
OR ( '2003-06-07 22:00:00.000' BETWEEN STARTDATE AND ENDDATE))

these are the tables used

CREATE TABLE [INFO_PLANNED_MAINTENANCE] (
[LINE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LINECODE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[YEAR] [int] NULL ,
[PERIOD] [smallint] NULL ,
[WEEK] [smallint] NULL ,
[SHIFT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MAINTENANCE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STARTDATE] [datetime] NULL ,
[ENDDATE] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF__INFO_PLAN__rowgu__7094766C] DEFAULT (newid())
) ON [PRIMARY]
GO

CREATE TABLE [INFO_BREAKDOWN] (
[BESCHRIJVING] [varchar] (130) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_BREAKDOWN_BESCHRIJVING] DEFAULT (' '),
[PLANNED] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_BREAKDOWN_PLANNED] DEFAULT ('N'),
[STARTDATUMTIJD] [datetime] NULL ,
[EINDDATUMTIJD] [datetime] NULL ,
[LNE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PERIOD] [decimal](18, 0) NULL ,
[WEEK] [decimal](18, 0) NULL ,
[DOWNTIME] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_BREAKDOWN_DOWNTIME] DEFAULT ('00:00'),
[ITEM] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID_REDEN] [int] NULL CONSTRAINT [DF_INFO_BREAKDOWN_ID_REDEN] DEFAULT (0),
[ID_SUBREDEN] [int] NULL CONSTRAINT [DF_INFO_BREAKDOWN_ID_SUBREDEN] DEFAULT (0),
[PREVIOUSITEM] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BREAKDOWNTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_INFO_BREAKDOWN_BREAKDOWNTYPE] DEFAULT ('L'),
[CREATE_DATE] [datetime] NULL CONSTRAINT [DF_INFO_BREAKDOWN_CREATE_DATE] DEFAULT (getdate()),
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF__INFO_BREA__rowgu__2F85CD1E] DEFAULT (newid())
) ON [PRIMARY]
GO

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-16 : 07:10:12


...
AND (( @StartDate BETWEEN dateadd(minute,datediff(minute,0,STARTDATE),0)
                  AND dateadd(minute,datediff(minute,0,ENDDATE),0)) OR
     ( @EindDate BETWEEN dateadd(minute,datediff(minute,0,STARTDATE),0)
                 AND dateadd(minute,datediff(minute,0,STARTDATE),0)))

Jay White
{0}

nmg196
Yak Posting Veteran

70 Posts

Posted - 2003-11-04 : 12:49:12

How would I do this, and exclude only the milliseconds part?

This:

select dateadd(s,datediff(s,0,getDate()),0)

...causes the following error: "Difference of two datetime columns caused overflow at runtime."

I need to compare two dates, but ignore the milliseconds part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 12:59:13

Convert it to smalldatetime.

SELECT DATEDIFF(s, CONVERT(SMALLDATETIME, @date1), CONVERT(SMALLDATETIME, @date2))

Tara

nmg196
Yak Posting Veteran

70 Posts

Posted - 2003-11-04 : 14:10:42

No that causes the seconds to be lost as well.

I *do* want to compare the seconds, but not the milliseconds...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 14:37:15

Then build your dates using DATEPART but leave out the milliseconds:

SELECT DATEPART(month, @date1) + '/' + DATEPART(day, @date1) + '/' + DATEPART(year, @date1) + ' ' + ...

and then do the DATEDIFF on those

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-04 : 15:35:58

Also,

where

convert(char(10),Field1,102) + convert(char(8),Field1,114) >

convert(char(10),Field2,102) + convert(char(8),Field2,114)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-11-04 : 18:46:56

This should work:

SELECT DateAdd(ms, -DatePart(ms, myDate), myDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources