Generate a list of dates given initial and final dates

idemnos

Forums

Good afternoon:

I have to simultate a sql process which start to create a sequence of dates given initial and final date, actually an improvement would be just capture the initial date because the process is 36 months long, and generate a row using the last day of the month.

Thanks in advance for any help

Brian Element Wed, 09/26/2018 - 08:32

Hello idemnos,

Just want to understand this a bit better.  So the script would create a new database with each row would have a date starting from the start date to the end date, is this what you are looking for or do you only need the last day of each month?

So would the new database look something like this:

20180101

20180102

20180103

and so on.  Is this what you are looking for?

Brian

idemnos Wed, 09/26/2018 - 10:55

Hi, there.

Sorry, if I wasn't clear enouch, my english is not that good.

Yes, I input a date: this date is the last date for the whole period. The script will create a database where each row is the last day of every month, going back 36 months. In example:

variable date= '20180831'

result:
row1 20180831
row2 20180730
...
row36 20150831

DanHoep Thu, 09/27/2018 - 05:58

Good Morning everyone,
this sounded like an interesting Idea (pun intended), so I wrote some Code. I couldn't find out how to add days to a date, so I wrote a special function for it:

Sub Main
Dim EndDate As Date
Dim MonthsBack As Long
Enddate = CDate(InputBox("Please enter a valid Date","",Date()))
MonthsBack = CLng(InputBox("Please enter number of Months Back","","36"))

If enddate <>"" And Monthsback <>"" Then
CreateTableWithDates EndDate,MonthsBack,"MyDateList.IMD",True
Client.OpenDatabase "MyDateList.IMD"
Client.RefreshFileExplorer
End If
End Sub

Sub CreateTableWithDates(EndDate As Date, MonthsBack As Long, DBName As String,IncludeEndDate As Boolean)
'Creates a database named DBName containing EndOfMonth-Dates for MonthsBack- Months
' Reaching to the EndDate. The EndDate can be included.
Dim i As Long

Dim NewTbl As Table
Dim NewFld As Field
Dim NewDB As Database

Dim myRecSet As Recordset
Dim tmpRecord As Record
Dim tmpDate As Date

'Create New Table with single field inside a Database
Set NewTbl = Client.NewTableDef
Set NewFld = NewTbl.NewField
NewFld.Name = "DateList"
NewFld.Type = WI_DATE_FIELD

NewTbl.AppendField NewFld
NewTbl.Protect = False

Set NewDB = Client.NewDatabase(DBName,"A list of " & MonthsBack & " End-Of-Month-Dates",NewTbl)

'CreateRecords
Set myRecSet = NewDB.RecordSet
For i = 1 + CLng(IncludeEndDate) To MonthsBack
Set tmpRecord = myRecSet.NewRecord
tmpDate = EndOfMonth(AddMonthsToDate(EndDate,-i))
tmpRecord.SetDateValueAt 1, Format$(tmpDate,"YYYYMMDD")
myRecSet.AppendRecord tmpRecord
Next i
NewTbl.Protect = True
NewDb.CommitDatabase
End Sub

Private Function EndOfMonth(dte As Date) As Date
EndOfMonth = AddDaysToDate(DateSerial(Year(dte),Month(dte)+1,1),-1)
End Function

Private Function AddDaysToDate(dte As Date, Days As Long) As Date
AddDaysToDate = DateSerial(Year(dte),Month(dte),Day(dte) + Days)
End Function

Private Function AddMonthsToDate(dte As Date,Months As Long) As Date
AddMonthsToDate = DateSerial(Year(dte),Month(dte)+Months,Day(dte))
End Function

Private Function AddYearsToDate(dte As Date, Years As Long) As Date
AddYearsToDate = DateSerial(Year(dte) + Years,Month(dte),Day(dte))
End Function

I hope it'll work out right and am happy for any of your remarks.

Daniel