May 12, 2010 at 11:14 am
i need to create a query for my report, on my report i'll have a date parameter, when a user enter the date i need to calculate schedules that were done in the past 6 months from the date entered and also what is scheduled from the date to 6 months in the future... it will be a crosstab report with the date as columns
May 12, 2010 at 11:21 am
Something like this?
DECLARE @entereddate DATETIME
SET @entereddate = GETDATE()
SELECT DATEADD(MONTH, -6, @entereddate) AS past,
@entereddate,
DATEADD(MONTH, 6, @entereddate) AS future
-- Cory
May 12, 2010 at 11:21 am
To get started with your T-SQL code go to:
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
This will give you code samples to do just about anything with date/time values.
Then if have additional questions come on back ....
May 12, 2010 at 11:31 am
DECLARE @date DATETIME, @int INTEGER
SET @date = GETDATE()
SET @int = 6
SELECT DATEADD(MONTH, -1*@int, @date) AS past,
@date,
DATEADD(MONTH, @int, @date) AS future
May 12, 2010 at 12:06 pm
Nomvula
i need to calculate schedules that were done in the past 6 months
DECLARE @date DATETIME, @int INTEGER
SET @date = GETDATE()
SET @int = 6
SELECT DATEADD(MONTH, -1*@int, @date) AS past,
@date,
DATEADD(MONTH, @int, @date) AS future
/* Yields:
past (No column name) future
2009-11-12 13:53:33.450 2010-05-12 13:53:33.450 2010-11-12 13:53:33.450
Which means that an item scheduled for 2010-11-12 14:00:00 in other words at 4 PM on November 12th would NOT be included. Is this what your users want or will they want to see ALL items scheduled on November 12th?
If not, then follow the link I posted previously, which will show you how to set the time portion of a datetime value to zero.
As an aside: To strip the time from GETDATE() use:
SELECT dateadd(dd, datediff(dd, 0, getdate()), 0)
/* which returns:
2010-05-12 00:00:00.000*/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply