Assemble monthly periods between dates into a string
The program collects a BEGIN date and an END date from the user;
these are stored in variables as dates: mdPeriodBEG and mdPeriodEND; no problem. I now need to construct a string variable that incorporates all the monthly periods in between, regardless of start and end dates given, in a certain format. Examples: 1. Example: Given BEG: 10-2007; END: 02-2008; * I need a string variable loaded with: 'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08' *Please note: crosses a calendar year 2. Example: Given BEG: 02-2008; END: 05-2008; I need a string variable loaded with: 'Feb-08', 'Mar-08', 'Apr-08', 'May-08' This format is required so I can ultimately feed this string variable of dates into a SQL string. I know how to concatenate a string variable, but I need an efficient method of doing so using variable start and end periods. Please note that due to the datatype being used in Oracle, I cannot simply use BETWEEN in my SQL string, rather I must feed it each individual period as shown in my example using the IN keyword. I am at a loss on how to do this efficiently, but perhaps a select case or two? Thanks much in advance for your kind assistance. |
Assemble monthly periods between dates into a string
Of course, 10-2007 and 02-2008 are not **real** VB dates; however, this
function should return the SQL string you are looking for whether you input your dates like you showed in your message or as real VB dates... Function GetSQLDates(mdPeriodBEG As Date, mdPeriodEND As Date) As String Dim X As Long GetSQLDates = "'" & Format(mdPeriodBEG, "mmm-yy") & "'" For X = 1 To DateDiff("m", mdPeriodBEG, mdPeriodEND) GetSQLDates = GetSQLDates & ", '" & _ Format(DateAdd("m", X, mdPeriodBEG), "mmm-yy") & "'" Next End Function Rick "XP" wrote in message ... The program collects a BEGIN date and an END date from the user; these are stored in variables as dates: mdPeriodBEG and mdPeriodEND; no problem. I now need to construct a string variable that incorporates all the monthly periods in between, regardless of start and end dates given, in a certain format. Examples: 1. Example: Given BEG: 10-2007; END: 02-2008; * I need a string variable loaded with: 'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08' *Please note: crosses a calendar year 2. Example: Given BEG: 02-2008; END: 05-2008; I need a string variable loaded with: 'Feb-08', 'Mar-08', 'Apr-08', 'May-08' This format is required so I can ultimately feed this string variable of dates into a SQL string. I know how to concatenate a string variable, but I need an efficient method of doing so using variable start and end periods. Please note that due to the datatype being used in Oracle, I cannot simply use BETWEEN in my SQL string, rather I must feed it each individual period as shown in my example using the IN keyword. I am at a loss on how to do this efficiently, but perhaps a select case or two? Thanks much in advance for your kind assistance. |
Assemble monthly periods between dates into a string
Hi Rick,
I ultimately came up with a solution, but it's not nearly as cohesive and efficient as yours; I am adding your solution to my code library, thanks much for the help! "Rick Rothstein (MVP - VB)" wrote: Of course, 10-2007 and 02-2008 are not **real** VB dates; however, this function should return the SQL string you are looking for whether you input your dates like you showed in your message or as real VB dates... Function GetSQLDates(mdPeriodBEG As Date, mdPeriodEND As Date) As String Dim X As Long GetSQLDates = "'" & Format(mdPeriodBEG, "mmm-yy") & "'" For X = 1 To DateDiff("m", mdPeriodBEG, mdPeriodEND) GetSQLDates = GetSQLDates & ", '" & _ Format(DateAdd("m", X, mdPeriodBEG), "mmm-yy") & "'" Next End Function Rick "XP" wrote in message ... The program collects a BEGIN date and an END date from the user; these are stored in variables as dates: mdPeriodBEG and mdPeriodEND; no problem. I now need to construct a string variable that incorporates all the monthly periods in between, regardless of start and end dates given, in a certain format. Examples: 1. Example: Given BEG: 10-2007; END: 02-2008; * I need a string variable loaded with: 'Oct-07', 'Nov-07', 'Dec-07', 'Jan-08', 'Feb-08' *Please note: crosses a calendar year 2. Example: Given BEG: 02-2008; END: 05-2008; I need a string variable loaded with: 'Feb-08', 'Mar-08', 'Apr-08', 'May-08' This format is required so I can ultimately feed this string variable of dates into a SQL string. I know how to concatenate a string variable, but I need an efficient method of doing so using variable start and end periods. Please note that due to the datatype being used in Oracle, I cannot simply use BETWEEN in my SQL string, rather I must feed it each individual period as shown in my example using the IN keyword. I am at a loss on how to do this efficiently, but perhaps a select case or two? Thanks much in advance for your kind assistance. |
All times are GMT +1. The time now is 05:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com