Hello all,
Is there any variation on the “dateadd” I’ve used in my calculated field below that will only add weekdays?
Thank you,
Alice
Yes here all the variations available as what you want to add to your date:
| datepart | Abbreviations |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Pierre
2 Likes
Thank you for your response. I am sure I am missing something here. I used the weekday datepart within the dateadd formula and it didn’t change anything. I may be misunderstanding how datepart is used. Learning curve.
I tested this and found it wasn’t working, so I checked the T-SQL reference:
datepart Argument
dayofyear, day, and weekday return the same value.
Shouldn’t weekday return a value of 1-7? Do I need to use SET DATEFIRST to make this work?
You indicated wanting to add weekdays… but isin’t that same as adding days?
You can add days, weeks, months and years… that should suffice to have a desired future date value.
I did not find a day of the week…in dateadd.
DATEPART(dw,Constants.Today) will give you with the day (int) of the week of the date provided. (Sunday being 1 )
Pierre
Pierre
1 Like
I feel like I am bit closer? Based on this vendorID, I want to add 5 business days to the order date. I am trying here to program this for “Sunday.” I am having syntax problems.
Thank you all.
Don’t you need an “else” at the end of that? What is it supposed to do if it isn’t Sunday?
Tried including all cases and simplified it a bit to troubleshoot (not including vendor ID as filter) and ended up with the same syntax error.
I would have to click around to figure out where that error is coming from (I’m not very good with plain SQL). If you take the field out altogether, does the error go away?
this should work
(case when DATENAME(WEEKDAY,POHeader.OrderDate ) = ‘Sunday’ then DATEADD(dd, 6,POHeader.OrderDate) else DATEADD(dd, 6,POHeader.OrderDate) end)
I think might be the colon around Sunday…? or the brackets …
Try it.
Pierre
It’s a different table date, but this is what I come up with using the functions listed in the BAQ builder. (except for date name, that one isn’t in the list, but still useable)
I don’t think it likes the brackets you have in there.
edit: changed the picture to what actually worked
1 Like
you were right about the colon being a problem initially. I made it work, thank you! This is what worked:
CASE WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Sunday'
THEN DATEADD(dd,5,POHeader.OrderDate)
WHEN DATENAME(WEEKDAY,POHeader.OrderDate) = 'Saturday'
THEN DATEADD(dd,6,POHeader.OrderDate)
ELSE DATEADD(dd,7,POHeader.OrderDate)
END
1 Like