Results 1 to 3 of 3

Thread: Date Query

  1. #1
    Join Date
    Feb 2002
    Posts
    9

    Question Unanswered: Date Query

    How to write a query which will return list of all the dates from say 3 days from current date and also next 3 days from current date..

    eg say

    Output should be something like this:

    Date
    -------------
    02/09/2002
    02/10/2002
    02/11/2002
    02/12/2002 <--- Today's date
    02/13/2002
    02/14/2002
    02/15/2002


    Any comments or suggestions are welcome.

    Thanks! in advance.

  2. #2
    Join Date
    Feb 2002
    Location
    Baltimore, MD
    Posts
    26
    Does SQL Server have a DUAL table, or something similar? In Oracle, I'd do:

    SELECT SYSDATE (getDate() in SQL Server)
    FROM DUAL
    WHERE getDate() BETWEEN DateAdd ( 'd', -3, getDate() ) AND DateAdd ( 'd', 3, getDate() )

    Obviously, that's a mix of Oracle and SQL Server syntax, but it should be fairly easy to tell which is which. The key is the DUAL table equivalent. I don't know if SQL Server has something like that. You might have to tweak the syntax a little, too. I rarely use the DateAdd function, myself, so I could be off a little.
    Rob Wilkerson

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    select convert(varchar(8),dateadd(dd,a.i, getdate()),112)
    from
    (
    select i = -3
    union select -2
    union select -1
    union select 0
    union select 1
    union select 2
    union select 3
    ) as a

    or using a temp table
    create table #a (d datetime)
    declare @d

    select @d = convert(varchar(8),dateadd(dd,-3, getdate()),112)
    while @d <= convert(varchar(8),dateadd(dd,3, getdate()),112)
    begin
    insert #a select @d
    select @d = dateadd(dd,1,@d)
    end
    select * from #a
    drop table #a

Posting Permissions

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