Using variables for the parameters in DATEADD

[TimK65]

Hello,

I am doing something a little weird (I'm withholding the details only so I don't bore anyone, although if I can't find a way to do this with DATEADD, I will probably have to explain more generally what I'm doing).

I am trying to generate a series of dates. These dates are essentially controlled by a parameter that indicates a starting date and a number of dates per year.

Because the number of dates per year may indicate that different intervals between dates would be appropriate, I tried setting a variable (@FreqIntvl) equal to "day", "week" or "month" as necessary and using that variable as the first parameter to the DATEADD function. However, this results in this error message:

Invalid parameter 1 specified for dateadd.

I've tried varying the datatype with which @FreqIntvl is defined, and I've tried enclosing the values assigned to it in quotation marks and not, and nothing seems to help. Is there a way to do what I'm trying to do?

The alternative is to simply write separate sections of code that handle the day, week and month cases, but since those sections of code will be essentially identical except for the words "day", "week" and "month", that seems really ugly.

Any help would be appreciated.

BTW, before I forget: I'm using Transact-SQL in SQL Server 2000 running under (I think) Windows Server 2003.

Thanks,
Tim Kynerd

[ck9663]

Hello,

I am doing something a little weird (I'm withholding the details only so I don't bore anyone, although if I can't find a way to do this with DATEADD, I will probably have to explain more generally what I'm doing).

I am trying to generate a series of dates. These dates are essentially controlled by a parameter that indicates a starting date and a number of dates per year.

Because the number of dates per year may indicate that different intervals between dates would be appropriate, I tried setting a variable (@FreqIntvl) equal to "day", "week" or "month" as necessary and using that variable as the first parameter to the DATEADD function. However, this results in this error message:

Invalid parameter 1 specified for dateadd.

I've tried varying the datatype with which @FreqIntvl is defined, and I've tried enclosing the values assigned to it in quotation marks and not, and nothing seems to help. Is there a way to do what I'm trying to do?

The alternative is to simply write separate sections of code that handle the day, week and month cases, but since those sections of code will be essentially identical except for the words "day", "week" and "month", that seems really ugly.

Any help would be appreciated.

BTW, before I forget: I'm using Transact-SQL in SQL Server 2000 running under (I think) Windows Server 2003.

Thanks,
Tim Kynerd

depending on your environment, setup, configuration,etc... you can either build a select string then do an exec @sqlstatement ..... or you can do a number of CASE..WHEN...

[TimK65]

depending on your environment, setup, configuration,etc... you can either build a select string then do an exec @sqlstatement ..... or you can do a number of CASE..WHEN...

Hmm.

I want to do what I'm doing in a stored procedure, or possibly a function (the whole thing will return a table). Can I do an exec @sqlstatement in that case?

I've already thought of doing this with a set of CASE statements, but again, since almost all the code would be identical, that goes against my programmer grain. :-)

Thanks,
Tim

[azimmer]

Hmm.

I want to do what I'm doing in a stored procedure, or possibly a function (the whole thing will return a table). Can I do an exec @sqlstatement in that case?

I've already thought of doing this with a set of CASE statements, but again, since almost all the code would be identical, that goes against my programmer grain. :-)

Thanks,
Tim

I suggest you use exec @sqlstatement. It's allowed in stored procedures; if you absolutely need a function you may write a wrapper (a function that only calls the procedure).

[TimK65]

I suggest you use exec @sqlstatement. It's allowed in stored procedures; if you absolutely need a function you may write a wrapper (a function that only calls the procedure).

Thanks to both of you for the suggestions. Ultimately I decided it was easier and more legible (I may not be at this job forever!) to use a CASE WHEN structure for this. It's a bit ugly, but then I had a case that required completely different processing anyway, so I think it's OK.

Best,
Tim