Results 1 to 6 of 6

Thread: Date problem

  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Unanswered: Date problem

    Hi,
    Can anybody help me to find an easy solution to this problem?
    I have a table

    CREATE TABLE T1 (
    Col1 VARCHAR(20)
    , Col2 VARCHAR(20)
    , Col3 VARCHAR(20)
    , Col4 DATETIME
    , Col5 INT )

    INSERT INTO T1 VALUES ('A01','B01','C01',23-03-2006,4)
    I want to pass a parameter to a stored proc such as Col1 ('A01'),and it will
    check value of Col5, which is 4 here in our data.And then it will generate a resultset by adding 1+ to the month of Col4.

    And I want to get a resultset like

    A01 B01 C01 23-03-2006
    A01 B01 C01 23-04-2006
    A01 B01 C01 23-05-2006
    A01 B01 C01 23-06-2006
    It will also check if the date is 25-12-2006 the next date would be 25-01-2007 and also if 29-01-2006 the next date would be 28-02-2006.
    I am trying to avoid Cursor.
    Any solution would be really appreciated.
    Thanks!!
    Last edited by rudra; 09-04-06 at 22:31.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create an integers table like this --
    Code:
    create table integers (i integer not null primary key) 
    insert into integers (i) values (0) 
    insert into integers (i) values (1) 
    insert into integers (i) values (2) 
    insert into integers (i) values (3) 
    insert into integers (i) values (4) 
    insert into integers (i) values (5) 
    insert into integers (i) values (6) 
    insert into integers (i) values (7) 
    insert into integers (i) values (8) 
    insert into integers (i) values (9)
    then in the stored proc, run this query --
    Code:
    select Col1
         , Col2
         , Col3
         , dateadd(mm,i,Col4) as Col4
      from integers
    cross
      join T1
     where i < Col5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    You are one of the smartest guy I ever seen.
    Rudy ,thanks a ton.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words

    but there are a half dozen guys in this very forum smarter than me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    create an integers table like this --
    Code:
    create table integers (i integer not null primary key) 
    insert into integers (i) values (0) 
    insert into integers (i) values (1) 
    insert into integers (i) values (2) 
    insert into integers (i) values (3) 
    insert into integers (i) values (4) 
    insert into integers (i) values (5) 
    insert into integers (i) values (6) 
    insert into integers (i) values (7) 
    insert into integers (i) values (8) 
    insert into integers (i) values (9)
    Just an FYI - if you want a BIG integers table (and one day you will ) this is a nice function to create one:
    http://sqljunkies.com/WebLog/amachan...bersTable.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    Just an FYI - if you want a BIG integers table (and one day you will ) this is a nice function to create one:
    http://sqljunkies.com/WebLog/amachan...bersTable.aspx
    Thanks for the link Pootie
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

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