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 |
|
sqlslick
Yak Posting Veteran
83 Posts |
 Posted - 2011-05-04 : 12:54:56
|
| Hello everyone,I need to pull data that searches in a field scheduled_datetime, which is of SMALLDATETIME data type. There will be two date parameters:1. @Begin_Date - Needs to be 1 month from current month starting with the first of the month (i.e. 2011/04/01 00:00:00) which is the same as 2011/04/01.2. @End_Date - Needs to be 2 months from current month ending with the end of the month (i.e. 2011/07:31 23:59:59) or for simplicity I will use 2011/08/01 00:00:00 which is the same as 2011/08/01In effort to try to achieve the above I am using the follwoing:@Begin_Date = SELECT DATEADD(MM, -1, GETDATE()) ... which gives me 2011-04-04 (today's date minus 1 month)@End_Date = SELECT DATEADD(MM, 3, GETDATE()) ... which gives me 2011-08-04 (today's date plus 3 months)This gives me the month ranges that I need but I need the dates to ALWAYS start with 01 or 1, the same as below:2011-04-012011-08-01Anybody have any ideas how I can achieve this?Thank you so much in advanced for any help and/or guidance you can provide.Regards,John |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-04 : 12:59:02
|
| select @Begin_Date = DATEADD(mm,datediff(mm,0,getdate())+1,0)select @End_Date = dateadd(ss,-1,dateadd(mm,3,@Begin_Date))==========================================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. |
 |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2011-05-04 : 14:30:04
|
| Hello,What I actually needed is below but you were the genious behind it all. Thank you so much for the fast reply. Now that I see the solution, I feel dumb ... LOL!SET @Begin_Date = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)SET @End_Date = DATEADD(ss, -1, DATEADD(mm, 4, @Begin_Date))Take care!! |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-04 : 16:25:02
|
| Be careful with adding seconds to a datetime and using that for you end date. Since this is a smalldatetime data type - it will work. But, if you were to change the data type to datetime or datetime2 - the precision is changed and you could then miss any data for that last second of the day.To get your end date - wouldn't it be easier to just add the number of months you want to the start date?SET @begin_date = dateadd(month, datediff(month, 0, getdate()) - 1, 0); -- first of previous monthSET @end_date = dateadd(month, 4, @begin_date);Then, in your where clause you will use an open interval range check:WHERE myDateColumn >= @begin_date -- includes midnight on first of monthAND myDateColumn < @end_date -- get everything up to the first of the month's end date - excluding midnightOr, you could set this up with the specific end date you want (at midnight) which works better for reports. For example:SET @begin_date = dateadd(month, datediff(month, 0, getdate()) - 1, 0); -- first of last monthSET @end_date = dateadd(month, datediff(month, -1, getdate()) + 2, -1); -- end of month 2 months in future (e.g. 20110731)Then, in your where clause you would use this:WHERE myDateColumn >= @begin_dateAND myDateColumn < dateadd(day, 1, @end_date)With both of these approaches, if the data type changes - the logic doesn't have to change and the code will still work. The only changes needed to the code would be a change to the data type for the variables.Jeff |
|
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2011-05-30 : 20:58:46
|
| Hey Jeff,Sorry about the late response. I ran into another problem since you cannot use variables in Views, but I was able to surpass this obstacle.Thank you very much for your input, however. You have some great ideas which I will certainly take into consideration in the future.Take care!John |
|
|
|
|
|
|
|
|
Using DATEADD Function