469,138 Members | 1,422 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Using variables for the parameters in DATEADD

3
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
Aug 28 '07 #1
4 6086
ck9663
2,878 Expert 2GB
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...
Aug 29 '07 #2
TimK65
3
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
Aug 29 '07 #3
azimmer
200 Expert 100+
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).
Aug 30 '07 #4
TimK65
3
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
Sep 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by simonjpaulger | last post: by
5 posts views Thread by Barkster | last post: by
reply views Thread by =?Utf-8?B?Sm9obm55?= | last post: by
17 posts views Thread by R.Rafii | last post: by
2 posts views Thread by franc sutherland | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.