Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Arrow Unanswered: Scripting with dates



    I am a beginner to SQL server. I am trying to create a script to ammend a date table to add a new week on a weekly basis, and remove the oldest week and ammend it to the archive table.

    Can anyone help, thanks.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What are you having problems with? Could you post some DDL?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2002
    Posts
    3
    told you I was a beginner, what do mean by DDL? Script?
    I have a database of dates and times for a year, i want to add a week and archive a week, each week.
    This is how I created the date table:

    USE testdb
    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[datetable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[datetable]
    GO

    CREATE TABLE [dbo].[datetable] (
    [slottable] [Smalldatetime] NOT NULL ,
    [dayofweek] [char] (10) NOT NULL ,
    [otherdata] [char] (10) NULL
    ) ON [PRIMARY]
    GO
    declare
    @Beginning_Date SmallDateTime,
    @Ending_Date SmallDateTime,
    @Temporary_Date SmallDateTime
    SET @beginning_Date = CAST('2002-04-01 00:00:00.000' AS smalldatetime)
    --select @beginning_date
    SET @temporary_Date = CAST('2002-04-01 00:00:00.000' AS smalldatetime)
    SET @ending_date = CAST('2003-03-31 00:00:00.000' AS smalldatetime)
    WHILE @temporary_date < DATEADD ( day , 1, @Ending_date )
    BEGIN
    set @temporary_date =
    DATEADD ( mi , 30, @temporary_date )
    while DATEPART ( hh , @temporary_date )between 07 and 18
    and DATEPART ( dw , @temporary_date ) between 2 and 6
    begin
    set @temporary_date =
    DATEADD ( mi , 30, @temporary_date )
    insert dbo.datetable
    values (@temporary_date,DATENAME ( weekday , @temporary_date ) , 'comments')

    END
    set @temporary_date = DATEADD ( hh , 6, @temporary_date )
    END
    select *
    from datetable



  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay try this

    Code:
    delete from Datetable where slottable between '2003-03-31 00:00:00' and '2003-03-31 23:59:59'
    
    declare @Monday datetime, @Friday datetime
    select @Monday = min(slottable) from datetable where datepart(dw,slottable) = 2
    -- Here are the days to insert and remove
    select * from DateTable where slottable between @Monday and dateadd(dd,5,@Monday)
    -- Here are the days to add
    select @Friday = max(slottable) from datetable where datepart(dw,slottable) = 6
    set @Monday = dateadd(dd,3,@Friday)
    set @Friday = dateadd(wk,1,@Friday)
    select @Monday, @Friday
    I delete the data fro '2003-03-31' so that my last entry would be a Friday. I also didn't write the while loop to add the dates because I wasn't sure of the time part as it looks like it varies, besides from your code it looks like you can build that part anyway.

    Clear as mud?
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2002
    Posts
    3
    This did not produce any results

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    maybe this will help:

    Code:
    delete from Datetable where slottable between '2003-03-31 00:00:00' and '2003-03-31 23:59:59'
    
    declare @Monday datetime, @Friday datetime
    select @Monday = min(slottable) from Datetable where datepart(dw,slottable) = 2
    print 'Insert and then delete records who''s slottable attribute is between ' + convert(varchar(20), @Monday,120) + ' and ' + convert(varchar(20), dateadd(s,-1,convert(varchar(20), dateadd(dd,5,@Monday),101)),120) + '.'
    select @Friday = max(slottable) from Datetable where datepart(dw,slottable) = 6
    set @Monday = convert( varchar(25),dateadd(dd,3,@Friday),101)
    set @Friday = convert( varchar(25),dateadd(wk,1,@Friday),101)
    print 'Add records for the following dates, From: ' + convert(varchar(25), @Monday,101) + ' To: ' + convert(varchar(25), @Friday,101) + '.'
    should produce the following:

    Insert and then delete records who's slottable attribute is between 2002-04-01 07:30:00 and 2002-04-05 23:59:59.
    Add records for the following dates, From: 03/31/2003 To: 04/04/2003.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •