Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arnae92
Starting Member
5 Posts |
 Posted - 2011-11-25 : 11:53:33
|
| I'm trying to write an expression that will check the dataset for the 19th of the month, if true then 19th start a new month, if false give me all the days from the 19th of that month through the 18th of the following month. The error message I'm getting is saying that ">=" is not defined for type "date" and type "integer". Can someone tell my how to accomplish this task. I'm using the following expression:=iif(CDate(Fields!Date.Value).day =19,CDate(Fields!Date.Value).AddMonths(1),DateDiff(dateinterval.Day,fields!Date.Value >= 19, fields!Date.Value) < 18) |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-25 : 11:59:09
|
| DateDiff(dateinterval.Day,fields!Date.Value >= 19, fields!Date.Value)What is this trying to do? It would return a number but it's not clear which two dates you are trying to compare.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
arnae92
Starting Member
5 Posts |
Posted - 2011-11-25 : 13:11:50
|
| I want to compare the date that's in the dataset. Our company's fiscal months are from the 19th of the month to the 18th of the following month. Like fiscal November month started on Oct 19 and will ended on Nov 18. Fiscal month Dec started Nov 19 and will end Dec 18. How can I check to see what date that's in the dataset and determine if this is a beginging of the month date? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-25 : 13:22:40
|
| I'm guessing you want to start off with iif(CDate(Fields!Date.Value).day >=19DateDiff(dateinterval.Day,fields!Date.Value >= 19, fields!Date.Value)the fields!Date.Value >= 19 here needs to be a date whereas this will return a boolean (true or false).Try to get away from the detail of the codeing and just write in pseudo code (or formatted english) what you want - then you should find writing the code easy.what you have here is I thinkif day = 19 then date + 1 month else (the difference in days between date and date >= 19) < 18)So the first part of the iif returns a date and the second a boolean - I'm guessing you want a date from both but not sure what that is.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
arnae92
Starting Member
5 Posts |
Posted - 2011-11-25 : 13:36:19
|
| In my report I'm trying to get all the days for the months on the year with the appropiate days. Like for the month of Nov, I would like for it's beginning date to be Oct 19 and all day from Oct 19 through Nov 18. So when use expands the month of Nov all the dates will be there. |
|
|
|
arnae92
Starting Member
5 Posts |
Posted - 2011-11-25 : 13:51:45
|
| I want to keep looping through the dataset and only going to the next month when date 19th is reached within the dataset |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-25 : 13:52:23
|
| so - take the previous 19th of the month and subtract the number of days difference to the given date. (or maybe 18th)The prcessing you want is justif day of month >= 19 then month of date else month of (add 1 month to date) endYou want returned a year,month - you can convert that to the first of the month or the 18/19th as you wish.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
working with date values in ssrs