Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: need to know if there can be an sql statement which can do the following

    for creating table that contains list of dates between 2 dates which can be used for joining purposes

    for example if i have 2 dates 1-1-2010 & 1-5-2010
    the table should contains the following

    1-1-2010
    1-2-2010
    1-3-2010
    1-4-2010
    1-5-2010

    thank you
    ----

    sorry i want access fourm
    Last edited by hish_r; 02-06-12 at 06:17.

  2. #2
    Join Date
    Feb 2012
    Posts
    6

    creating table that contains list of dates between 2 dates

    how i can creating table that contains list of dates between 2 dates which can be used for joining purposes

    for example if i have 2 dates 1-1-2010 & 1-5-2010
    the table should contains the following

    1-1-2010
    1-2-2010
    1-3-2010
    1-4-2010
    1-5-2010

    thank you

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a new table called numbers, containing only one column called n, the primary key, and populate this table with the numbers 0 through whatever large number you want that will cover the number of dates between your first date and last date

    then
    Code:
    SELECT DATEADD('day',n,'2010-01-01') AS thedate
      FROM numbers
     WHERE DATEADD('day',n,'2010-01-01') <= '2010-05-01'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Her's a possibility (using VBA):
    Code:
    Public Sub CreateDateRangeTable(ByVal TableName As String, ByVal Date1 As Date, ByVal Date2 As Date)
    
        Const c_SQL As String = "INSERT INTO @T ( Dte ) VALUES ( @V );"
        
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim cnt As Long
        Dim i As Long
        
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If tdf.Name = TableName Then
                dbs.Execute "DROP TABLE " & TableName & ";", dbFailOnError
                Exit For
            End If
        Next tdf
        Set tdf = Nothing
        dbs.Execute "CREATE TABLE " & TableName & " (Dte DATETIME CONSTRAINT PrimaryKey PRIMARY KEY);", dbFailOnError
        cnt = DateDiff("d", Date1, Date2)
        For i = 0 To cnt
            dbs.Execute Replace(Replace(c_SQL, "@T", TableName), "@V", "#" & DateAdd("d", i, Date1) & "#")
        Next i
        Set dbs = Nothing
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Feb 2012
    Posts
    6

    thx

    thx alot
    but do you have another way without using the DATEADD ?!\

  6. #6
    Join Date
    Feb 2012
    Posts
    6

    thank you

    thank you
    very much

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with using DATEADD?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2012
    Posts
    6
    sorry there is no problem in using it
    but the problem is in my database which doesn't support it
    i'm thinking if there could be away by using basic sql statements without functions

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hish_r View Post
    but the problem is in my database which doesn't support it
    what kind of database is that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2012
    Posts
    6
    i think dbisam version 4

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dbisam? i've never heard of that

    are you sure that's a microsoft access database?

    note: you posted in the microsoft access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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