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

hish_r · #1 (**permalink**) 02-06-12, 05:00

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

hish_r · #2 (**permalink**) 02-06-12, 05:20

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

r937 · #3 (**permalink**) 02-06-12, 06:35

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'

Sinndho · #4 (**permalink**) 02-06-12, 06:54

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

hish_r · #5 (**permalink**) 02-06-12, 06:58

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

hish_r · #6 (**permalink**) 02-06-12, 07:10

thank you

very much

r937 · #7 (**permalink**) 02-06-12, 07:11

what's wrong with using DATEADD?

hish_r · #8 (**permalink**) 02-06-12, 07:22

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