Generate a list of dates given initial and final dates
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
Hi, there.
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
Good Morning everyone,
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
HI Daniel, thanks for posting
HI Daniel, thanks for posting the script. I did a more expanded script that allows for all dates, beginning and ending of month.
Hello idemnos,
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