Professional Web Applications Themes

SQL Dates... - Microsoft SQL / MS SQL Server

Hi I have a start date and an end date. I need a select statement that will, starting from the start date, return a recordset consisting of the date at X days interval upto the end date. Eg startdate 1-1-2004 enddate 1-2-2004 recordset should look like this where X=7 days... 1-1-2004 8-1-2004 15-1-2004 22-1-2004 29-1-2004 etc Anybody got any ideas?...

  1. #1

    Default SQL Dates...

    Hi

    I have a start date and an end date.

    I need a select statement that will, starting from the
    start date, return a recordset consisting of the date at X
    days interval upto the end date.

    Eg startdate 1-1-2004 enddate 1-2-2004
    recordset should look like this where X=7 days...
    1-1-2004
    8-1-2004
    15-1-2004
    22-1-2004
    29-1-2004
    etc


    Anybody got any ideas?

    Paul Guest

  2. #2

    Default Re: SQL Dates...

    Paul O'Brien wrote: 

    You're going to have to show us your table structure (DDL really helps
    remove any ambiguity about your data types - relevant columns only, please),
    sample data, and desired results from that sample data.

    Bob Barrows


    Bob Guest

  3. #3

    Default Re: SQL Dates...

    pls post DDL/some sample records for accurate result.
    Im not sure what you are looking for.

    I assume you want the records between the start date
    and x days after the start date.

    Ex:
    declare start_date datetime
    set start_date = 'yyyymmdd' --use this format only
    select * from table
    where dt_field between start_date and dateadd(dd,x,start_Date) --x = no of
    days to be added.

    --
    -Vishal

    "Paul O'Brien" <com> wrote in message
    news:067601c35701$d20347c0$gbl... 


    Vishal Guest

  4. #4

    Default Re: SQL Dates...

    Paul,
     

    You can do this with a table of sequential numbers.

    -- Create the table of snumbers.
    select identity(int, 0, 1) nbr into seq from master..syscolumns

    declare from datetime
    declare thru datetime
    declare interval int
    select from = '20040101', thru = '20040201', interval = 7

    select nbr, dateadd(dd, nbr, from) 'date'
    from seq
    where nbr % interval = 0
    and dateadd(dd, nbr, from) <= thru
    go
    drop table seq


    nbr date
    ----------- ---------------------------
    0 2004-01-01 00:00:00.000
    7 2004-01-08 00:00:00.000
    14 2004-01-15 00:00:00.000
    21 2004-01-22 00:00:00.000
    28 2004-01-29 00:00:00.000


    Linda

    lindawie Guest

Similar Threads

  1. Between Dates
    By javman in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: June 7th, 02:50 PM
  2. Dates & SQL
    By smokin_joe in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 29th, 04:56 PM
  3. Dates again
    By Clive Moss in forum ASP Database
    Replies: 4
    Last Post: January 14th, 03:35 AM
  4. Dates
    By Support in forum PERL Beginners
    Replies: 0
    Last Post: August 19th, 08:35 AM
  5. Help with Dates please
    By Ian Piper in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 07:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139