Generate a list of dates given initial and final dates

8 posts / 0 new
Last post
idemnos's picture
idemnos
Offline
Joined: 06/17/2016 - 13:25
Generate a list of dates given initial and final dates

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's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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's picture
idemnos
Offline
Joined: 06/17/2016 - 13:25

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

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Thanks for the clarrification.  I will see what I can put together for you.

Brian

idemnos's picture
idemnos
Offline
Joined: 06/17/2016 - 13:25

Thanks a lot, Brian!

DanHoep
Offline
Joined: 09/13/2018 - 05:37

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

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

HI Daniel, thanks for posting the script.  I did a more expanded script that allows for all dates, beginning and ending of month.

http://ideascripting.com/ideascript/date-creation-database

idemnos's picture
idemnos
Offline
Joined: 06/17/2016 - 13:25

Thanks a lot, guys! I hadn't got time to replay, sorry about that. These solutions are very useful.