SQL Server Performance

Need some help on date/time comparison

Discussion in 'General Developer Questions' started by dyckwal, Jun 15, 2003.

  1. dyckwal New Member

    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
  2. bambola New Member

    To remove the milliseconds you can use select convert(varchar(19), '2003-06-07 22:00:00.233'). Not sure that is what you are looking for. What exactly is the problem with this query?

    Bambola.
  3. dyckwal New Member

    I wanted to avoid that some date/time combinations would fall into a frame of the info_planned_maintenance table. This one holds the date and time of planned production stops. On insert into INFO_BREAKDOWN I take the inserted date/time fileds a look for a match in the info_planned_maintenance. If match found @Type is changed accordingly.

    I have been doing some testing with various date/time combinations, and as it seems I do not have to worry. It does not return false matches.

    Tips on tuning the code are always welcome.

    Thanks

Share This Page