October 11, 2011 at 9:46 am
Hi
I want to be able to calucualte 2 dates based on todays date:
ie today is 11/10/2011 (uk date) I want to be able to return start date as 01/10/2010 and end date as 01/10/2011. In the same way if todays date was 25/08/2009 I would want to return start date of 01/08/2009 and end date of 01/08/2010.
Is this possible???
Thanks
Carl.
October 11, 2011 at 9:52 am
I believe so.
Should give you
DECLARE @StDt DATE, @EndDt DATE, @Now DATE
SET @Now = GETDATE()
SELECT @StDt = DATEADD( dd, ( -1 * DATEPART( dd, @Now ) ) + 1, @Now )
SELECT @EndDt = DATEADD( yy, -1, @StDt )
SELECT 'Now' = @Now, StDt = @StDt, EndDt = @EndDt
That work for you?
CEWII
October 11, 2011 at 9:53 am
does the following help...courtesy of Lynn Pettis
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
declare @ThisDate datetime;
set @ThisDate = getdate();
select dateadd(dd, datediff(dd, 0, @ThisDate), 0) -- Beginning of this day
select dateadd(dd, datediff(dd, 0, @ThisDate) + 1, 0) -- Beginning of next day
select dateadd(dd, datediff(dd, 0, @ThisDate) - 1, 0) -- Beginning of previous day
select dateadd(wk, datediff(wk, 0, @ThisDate), 0) -- Beginning of this week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) + 1, 0) -- Beginning of next week (Monday)
select dateadd(wk, datediff(wk, 0, @ThisDate) - 1, 0) -- Beginning of previous week (Monday)
select dateadd(mm, datediff(mm, 0, @ThisDate), 0) -- Beginning of this month
select dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0) -- Beginning of next month
select dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0) -- Beginning of previous month
select dateadd(qq, datediff(qq, 0, @ThisDate), 0) -- Beginning of this quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) + 1, 0) -- Beginning of next quarter (Calendar)
select dateadd(qq, datediff(qq, 0, @ThisDate) - 1, 0) -- Beginning of previous quarter (Calendar)
select dateadd(yy, datediff(yy, 0, @ThisDate), 0) -- Beginning of this year
select dateadd(yy, datediff(yy, 0, @ThisDate) + 1, 0) -- Beginning of next year
select dateadd(yy, datediff(yy, 0, @ThisDate) - 1, 0) -- Beginning of previous year
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 11, 2011 at 9:54 am
Perfect with the exception of I wouldnt want the time on the end
October 11, 2011 at 9:56 am
And by using the DATE datatype you don't get it..
CEWII
October 11, 2011 at 8:50 pm
for date calculations I find it best do build it in steps
-- the date of 12 months ago
select DATEADD(m,-12,getdate())
-- gets the difference between the day of the month and the first day
select (DATEPART(d, DATEADD(m,-12,getdate()))*-1)+1
-- put it all together in a date add, then cast
select cast(DATEADD(d,(DATEPART(d, DATEADD(m,-12,getdate()))*-1)+1, DATEADD(m,-12,getdate())) as DATE)
October 11, 2011 at 11:56 pm
It could be done through variety of solutions like using splitting date values using varchar (n) or substring or left or right
But i do believe more with what Elliott Whitlow stated above where we use specific date function that deal well with end days (30,29,31,28) accroding to monthes
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply