February 15, 2010 at 3:51 am
Hi,
Hopefully someone can help me find the best solution to my problem.
I have a report that a user enters a start date and an end date. The user should ensure that the end dates day matches the start dates day, if they don't then I need to change the end date in code. For example if the start date is 2010-01-29 then the end date can be any month but the day needs to be 29th.
What is the best solution to achieve this? I have written some code that captures the day part of the start date and changes the day part of the end date. I have no idea if this is the best way to achieve what I’m after so advice welcome.
DECLARE @StartDateDATETIME
DECLARE @EndDateDATETIME
DECLARE @NewEndDateDATETIME
DECLARE @Yearvarchar(4)
DECLARE @Monthvarchar(2)
DECLARE @Dayvarchar(2)
SET @StartDate = '2010-01-29'
SET @EndDate = '2010-06-15'
SET @Year = DATEPART(yy,@StartDate)
SET @Month = DATEPART(mm,@StartDate)
SET @Day = DATEPART(dd,@StartDate)
SET @Year = DATEPART(yy,@EndDate)
SET @Month = DATEPART(mm,@EndDate)
SET @Day = DATEPART(dd,@EndDate)
SET @NewEndDate = @Month +'-'+ @Day +'-'+ @Year
--Sets the Enddates day to the same as the Startdates day
IF DATEPART(dd,@StartDate) <> DATEPART(dd,@NewEndDate)
BEGIN
SET @Year = DATEPART(yy,@EndDate)
SET @Month = DATEPART(mm,@EndDate)
SET @Day = DATEPART(dd,@StartDate)
SET @NewEndDate = @Month +'-'+ @Day +'-'+ @Year
END
ELSE
SET @NewEndDate = @EndDate
SELECT @NewEndDate
I'm struggling to work out how to take into account months like Feb who only have 28 days. At present this code would error if the end date was in Feb as there are only 28 days not 29.
All help appreciated, thanks.
February 15, 2010 at 4:17 am
Obviously we nat have 30th Feb so the business logic has to change. I would suggest trapping February and if End Day > 28 then End Day = 28 (and allow 29 when End year is a Leap Year).
Hope that helps
February 15, 2010 at 4:28 am
You need to go back to whoever defined the requirement that the enddate be the same day of the month as the startdate and check what they want done for shorter months. The obvious alternatives are either last day of the month or first day of next month.
The following will simply use the last day of the month.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @NewEndDate DATETIME
DECLARE @Year varchar(4)
DECLARE @Month varchar(2)
DECLARE @Day varchar(2)
SET @StartDate = '2010-01-29'
SET @EndDate = '2010-02-15'
SET @Year = convert(varchar(4),DATEPART(yy,@EndDate))
SET @Month = case when DATEPART(mm,@EndDate) < 10
then '0'
else ''
end + convert(varchar(2),DATEPART(mm,@EndDate))
SET @Day = case when DATEPART(dd,@StartDate) > DATEPART(dd,dateadd(d,-1,dateadd(m,1,@year+@month+'01')))
then convert(varchar(2),DATEPART(dd,dateadd(d,-1,dateadd(m,1,@year+@month+'01'))))
else case when DATEPART(dd,@StartDate) < 10
then '0'
else ''
end + convert(varchar(2),DATEPART(dd,@StartDate))
end
SET @NewEndDate = @Year+@Month+@Day
--Sets the Enddates day to the same as the Startdates day
SELECT @NewEndDate
Derek
February 15, 2010 at 6:40 am
Cheers for this, works a treat.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply