Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allocate Value Across Monthly Periods | Excel Worksheet Functions | |||
Comparing data from 2 monthly periods in 1 chart | Charts and Charting in Excel | |||
How to subtract/add periods in monthly base? | Excel Discussion (Misc queries) | |||
Dates and periods | Excel Worksheet Functions | |||
Number of semi-monthly periods between 2 dates | Excel Worksheet Functions |