April 3, 2012 at 1:21 pm
Trying to put together a generic procedure or function that I can pass a date and some parametes to and have it return a start and end date that I can put into @StartDate and @EndDate for use in my select statements in a larger multi part procedure.
Here is what I am having to do now -
DECLARE @DateRange TABLE
(
StartDatevarchar(50),
EndDatevarchar(50)
)
Declare @StartDate varchar(50)
Declare @EndDate varchar(50)
insert @DateRange
exec [dbo].[rpt_sp_get_date_range] '2012-04-02 00:00:00.001','W',-1
set @StartDate = (Select StartDate From @DateRange)
Set @EndDate = (Select EndDate From @DateRange)
The output from the stored proc [dbo].[rpt_sp_get_date_range] looks like this.
StartDate EndDate
2012-04-01 00:00:00.0002012-04-07 23:59:59.999
Looking for a simple way to call a this function and set these variable so I can use the same approach for all queries using date parametrs.
April 3, 2012 at 1:24 pm
Post the code for [dbo].[rpt_sp_get_date_range].
April 3, 2012 at 1:31 pm
Can you define "StartDate" and "EndDate"?
Is startdate the Sunday/Monday of the week that your input date falls on and the EndDate would be exactly 7 days from that sunday/monday?
As lynn asked, can you tell us what the SP does?
April 3, 2012 at 1:34 pm
ColdCoffee (4/3/2012)
Can you define "StartDate" and "EndDate"?Is startdate the Sunday/Monday of the week that your input date falls on and the EndDate would be exactly 7 days from that sunday/monday?
As lynn asked, can you tell us what the SP does?
Based on what has been posted, I think it depends on the parameters passed to the proc, which is why I asked to see the proc.
April 3, 2012 at 1:34 pm
Do not need the table variable.
Just use two output parameters. And, you are done.;-)
April 3, 2012 at 1:41 pm
Lynn Pettis (4/3/2012)
ColdCoffee (4/3/2012)
Can you define "StartDate" and "EndDate"?Is startdate the Sunday/Monday of the week that your input date falls on and the EndDate would be exactly 7 days from that sunday/monday?
As lynn asked, can you tell us what the SP does?
Based on what has been posted, I think it depends on the parameters passed to the proc, which is why I asked to see the proc.
Readin OP's question again, i sense that OP needs us to give him a heads up to create the SP.
SP will accept 3 parameters - input date ,date component (as in weeks or months or days or year) and the range to add to
So we pass date1 , 'Week',1 - results should be date1's (week +1 )'s sunday and date1's (week +1)'s satruday ..
April 3, 2012 at 1:54 pm
Wildcat is right, just use 2 OUTPUT parameters instead of table.
April 3, 2012 at 2:25 pm
The stored proc is not complete. I have hard coded the dates for testing and will add logic so that a frequency (D,W,M,Y) and and offset can be passed.
Here is the code as it stands that passes back a start and end date.
Declare @NewEnd varchar(50)
Declare @NewStart varchar(50)
Declare @starttime varchar(50)
set @starttime=' 00:00:00.000'
Declare @endtime varchar(50)
set @endtime=' 23:59:59.999'
Set @NewStart = DATEADD(wk,DATEDIFF(wk,7,'2012-04-01'),6) --Current Week Prior Sunday
Set @NewEnd = DATEADD(wk,DATEDIFF(wk,0,'2012-04-01'),5) --Next week next Saturday
Select Convert(varchar(50),
RIGHT('0'+CONVERT(VARCHAR,DATEPART(YYYY, @NewStart)),4)+ '-' +
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @NewStart)),2) + '-' +
RIGHT('0'+CONVERT(VARCHAR,DATEPART(DD, @NewStart)),2) + @starttime
) as [StartDate]
,
Convert(varchar(50),
RIGHT('0'+CONVERT(VARCHAR,DATEPART(YYYY, @NewEnd)),4)+ '-' +
RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @NewEnd)),2) + '-' +
RIGHT('0'+CONVERT(VARCHAR,DATEPART(DD, @NewEnd)),2) + @endtime
) as [EndDate]
April 3, 2012 at 2:36 pm
I added the code for rpt_sp_get_date_range.
April 3, 2012 at 2:56 pm
How about this?
DECLARE @InputDate DATETIME
SELECT @InputDate = '2012-04-08'
SELECT @InputDate = DATEADD(DD,DATEDIFF(DD,0, @InputDate),0)
;WITH OffsetCalendar (DayNam , StartIndex, EndIndex ) AS
(
SELECT 'Sunday' , 0 ,6
UNION ALL SELECT 'Monday' , -1 ,5
UNION ALL SELECT 'Tuesday' , -2 ,4
UNION ALL SELECT 'Wednesday' , -3 ,3
UNION ALL SELECT 'Thrusday' , -4 ,2
UNION ALL SELECT 'Friday' , -5 ,1
UNION ALL SELECT 'Saturday' , -6 ,0
)
SELECT StartDate = DATEADD(DD,OC.StartIndex,@InputDate)
,EndDate = DATEADD(DD,OC.EndIndex,@InputDate)
FROM OffsetCalendar OC
WHERE OC.DayNam = DATENAME(WEEKDAY, @InputDate)
April 3, 2012 at 4:01 pm
Extending the code:
DECLARE @InputDate DATETIME
-- Possible values 'DY' / 'WK' / 'MT' / 'YR'
-- DY - representing Days - produces the start of the day and end of day
-- YR - representing Year - produces the start of the year and last day of the year
-- WK - Week - finds the last Sunday of the @InputDate and first saturday after of the @InputDate
-- MT - Month - produces the start of the month and last day of the month
,@InputDatePart VARCHAR(2)
-- The +/- offset of the range of dates to cover
-- If this is for the the current week, use 0
-- if the range should span more than the curren week/month/year/day, use value > 0
,@InputOffset INT
;
-- Initialize the local variables
SELECT @InputDate = '2012-04-08'
,@InputDatePart = 'WK'
,@InputOffset = 0
;
-- "Round" the date
SELECT @InputDate = DATEADD(DD,DATEDIFF(DD,0, @InputDate),0)
;
IF @InputDatePart = 'WK'
BEGIN
-- List of offset indexes
-- Code asuumes Sunday is the start of the week
-- If Monday, then the index values will vary
;WITH OffsetCalendar (DayNam , StartIndex, EndIndex ) AS
(
SELECT 'Sunday' , 0 ,6
UNION ALL SELECT 'Monday' , -1 ,5
UNION ALL SELECT 'Tuesday' , -2 ,4
UNION ALL SELECT 'Wednesday' , -3 ,3
UNION ALL SELECT 'Thrusday' , -4 ,2
UNION ALL SELECT 'Friday' , -5 ,1
UNION ALL SELECT 'Saturday' , -6 ,0
)
SELECT StartDate = DATEADD( WEEK ,-1 * @InputOffset , DATEADD(DD,OC.StartIndex,@InputDate) )
,EndDate = DATEADD( WEEK ,@InputOffset , DATEADD(DD,OC.EndIndex,@InputDate) )
FROM OffsetCalendar OC
WHERE OC.DayNam = DATENAME(WEEKDAY, @InputDate)
END
;
-- Days
IF @InputDatePart = 'DY'
BEGIN
SELECT StartDate = DATEADD( DAY ,-1 * @InputOffset ,@InputDate )
,EndDate = DATEADD( DAY , @InputOffset ,DATEADD(MILLISECOND,-3, DATEADD(DD,1,@InputDate)) )
END
;
-- Year
IF @InputDatePart = 'YR'
BEGIN
SELECT @InputDate = DATEADD(YY,DATEDIFF(YY,0, @InputDate),0)
SELECT StartDate = DATEADD( YEAR ,-1 * @InputOffset ,@InputDate)
,EndDate = DATEADD( YEAR , @InputOffset ,DATEADD(MILLISECOND,-3, DATEADD(YY,1,@InputDate)))
END
;
-- MOnths
IF @InputDatePart = 'MT'
BEGIN
SELECT @InputDate = DATEADD(MM,DATEDIFF(MM,0, @InputDate),0)
SELECT StartDate = DATEADD( MONTH ,-1 * @InputOffset ,@InputDate)
,EndDate = DATEADD( MONTH , @InputOffset ,DATEADD(MILLISECOND,-3, DATEADD(MM,1,@InputDate)))
END
;
April 4, 2012 at 6:32 pm
schillingt (4/3/2012)
The output from the stored proc [dbo].[rpt_sp_get_date_range] looks like this.StartDate EndDate
2012-04-01 00:00:00.0002012-04-07 23:59:59.999
Boy, are you in for a surprise when you finally move the code to production. Run this and see what I mean and then pray you don't have any "whole dates" in your data. 😉
SELECT CAST('2012-04-07 23:59:59.999' AS DATETIME)
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2012 at 7:24 pm
Jeff Moden (4/4/2012)
schillingt (4/3/2012)
The output from the stored proc [dbo].[rpt_sp_get_date_range] looks like this.StartDate EndDate
2012-04-01 00:00:00.0002012-04-07 23:59:59.999
Boy, are you in for a surprise when you finally move the code to production. Run this and see what I mean and then pray you don't have any "whole dates" in your data. 😉
SELECT CAST('2012-04-07 23:59:59.999' AS DATETIME)
True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..
April 4, 2012 at 7:29 pm
I had already dropped the miliseconds due to this issue. I agree that I should drop the time stamp however even though all our date fields are defined as DATETIME and the selection logic seemed to work the same both ways with and without the 23:59:59 appended.
Thank you all for the help oin this.
April 4, 2012 at 7:31 pm
ColdCoffee (4/4/2012)
Jeff Moden (4/4/2012)
schillingt (4/3/2012)
The output from the stored proc [dbo].[rpt_sp_get_date_range] looks like this.StartDate EndDate
2012-04-01 00:00:00.0002012-04-07 23:59:59.999
Boy, are you in for a surprise when you finally move the code to production. Run this and see what I mean and then pray you don't have any "whole dates" in your data. 😉
SELECT CAST('2012-04-07 23:59:59.999' AS DATETIME)True, Jeff. Thats why in my code, i used -3 for milliseconds. OP posted this in 2008 forums, so i think its better to ask him to use DATETIME2..
And that is why I prefer somedatecolumn >= @startdate and somedatecolumn < @enddate. For example, I want all records for the month April 2012; @startdate = '2012-04-01 00:00:00.000' and @enddate = '2012-05-01 00:00:00.000'.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply