Month Day Parameter

Hi, I'm using CR2013. I am bringing in a SQL Command that will require a begin and end parameter, for begin month/day and end month/day. The field name is LAW_TAEEMASTER.MASTR_ENTRY.

I need the report to return only records where the month and day of LAW_TAEEMASTER.MASTR_ENTRY falls between the parameter. Year is not needed. For example, if my MASTR_ENTRY is 06/30/2017, and the parameter I enter is 6/25 to 7/4, this record should appear, regardless of year.

I am not sure how to construct this formula within the SQL. Your help is appreciated. I will post more code if needed. Once I know how to parse out month and day from the year and create a parameter for it though, I should be good to go. Thank you,

are you going to allow users to make the second param < the first param? Say 12-1 to 1-15?

Ah, good point, and yes, that would be the preferred way to construct the parameter. Thank you for clarifying.

something to consider would be maybe using cascading parameters to make sure the user selects a valid month/day combination. Think you would have to exclude leap year date as an option.

Assuming you have month and day integer values for a start and end I THINK the this would do what you want but look it over carefully


(
date(year(today),{?Month Start},{?Day Start})<=date(year(today),{?Month End},{?Day End})
and
dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) in date(year(today),{?Month Start},{?Day Start}) to date(year(today),{?Month End},{?Day End})
)
OR
(
date(year(today),{?Month Start},{?Day Start})>date(year(today),{?Month End},{?Day End})
and
    (
    dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) <= date(year(today),{?Month End},{?Day End})
    or
    dateadd('yyyy',datediff('yyyy',{LAW_TAEEMASTER.MASTR_ENTRY},today),{LAW_TAEEMASTER.MASTR_ENTRY}) >= date(year(today),{?Month Start},{?Day Start})
    )
)

Edited by DBlank - 05 Jul 2017 at 7:53am

Thank you! I thing this will work, but first, I get an error on this parameter "Failed to retrieve data from the database, 42000[Microsoft][OSBC SQL Server Driver]syntax error or access violation". If I take the parameter out, or change it to a simple date range parameter it works: LAW_TAEEMASTER.MASTR_ENTRY >= {?BegMasterEntryDate} and LAW_TAEEMASTER.MASTR_ENTRY <={?EndMasterEntryDate}. If you have any thoughts on that error please share. Not finding much on line.


Edited by thummel1 - 05 Jul 2017 at 8:48am

I was creating a crystal select using crystal parameters as compared to command parameters.
If you use command parameters you are going to run the risk of users entering non-date values.