May 16, 2012 at 2:59 pm
hi,
i want last day last year,what is the function for that.
example if i put date 02/29/12 the i should get 12/31/11.
if 03/31/2011 then it should be 12/31/2010
May 16, 2012 at 3:02 pm
This?
declare @date date = '02/29/12'
select dateadd(DD, -1, dateadd(YY,datediff(yy,0,@date),0))
May 16, 2012 at 3:20 pm
You can find some useful date routines here: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.
May 16, 2012 at 3:53 pm
thanks,it worked in sql.
but nit in ssrs expression.
any idea?
May 16, 2012 at 4:00 pm
riya_dave (5/16/2012)
thanks,it worked in sql.but nit in ssrs expression.
any idea?
Nope, don't have the Visual Basic.NET documentation available here.
May 16, 2012 at 10:08 pm
ColdCoffee (5/16/2012)
This?
declare @date date = '02/29/12'select dateadd(DD, -1, dateadd(YY,datediff(yy,0,@date),0))
This also works with one less function call.
select dateadd(yy,datediff(yy,-1,@date),-1)
May 16, 2012 at 10:09 pm
riya_dave (5/16/2012)
thanks,it worked in sql.but nit in ssrs expression.
any idea?
You should ask Reporting Services questions in the Reporting Services forum, instead of a TSQL forum.
May 17, 2012 at 4:12 am
Lynn Pettis (5/16/2012)
riya_dave (5/16/2012)
thanks,it worked in sql.but nit in ssrs expression.
any idea?
Nope, don't have the Visual Basic.NET documentation available here.
As a tribute to Lynn, I followed the link and converted all the examples to SSRS (VBA). 0 day is not used in the same way so need to use "1900/01/01" and a few adjustments on the duration types but the logic is the same.
The interval parameter should be one of the following value:
* "yyyy" Year
* "q" Quarter
* "m" Month
* "y" Day of year (day number of year not since 1/1/1900)
* "d" Day
* "w" Weekday (day number of week not since 1/1/1900)
* "ww" Week
* "h" Hour
* "n" Minute
* "s" Second
------------------------------------ SQL Server Reporting Services
/* Assuming data set with a field RelativeDate which holds a datetime */
=dateadd("d", datediff("d", "1900/01/01", Fields!RelativeDate.Value), "1900/01/01") -- Beginning of this day
=dateadd("d", datediff("d", "1900/01/01", Fields!RelativeDate.Value) + 1, "1900/01/01") -- Beginning of next day
=dateadd("d", datediff("d", "1900/01/01", Fields!RelativeDate.Value) - 1, "1900/01/01") -- Beginning of previous day
=dateadd("ww", datediff("ww", "1900/01/01", Fields!RelativeDate.Value), "1900/01/01") -- Beginning of this week (Monday)
=dateadd("ww", datediff("ww", "1900/01/01", Fields!RelativeDate.Value) + 1, "1900/01/01") -- Beginning of next week (Monday)
=dateadd("ww", datediff("ww", "1900/01/01", Fields!RelativeDate.Value) - 1, "1900/01/01") -- Beginning of previous week (Monday)
=dateadd("m", datediff("m", "1900/01/01", Fields!RelativeDate.Value), "1900/01/01") -- Beginning of this month
=dateadd("m", datediff("m", "1900/01/01", Fields!RelativeDate.Value) + 1, "1900/01/01") -- Beginning of next month
=dateadd("m", datediff("m", "1900/01/01", Fields!RelativeDate.Value) - 1, "1900/01/01") -- Beginning of previous month
=dateadd("q", datediff("q", "1900/01/01", Fields!RelativeDate.Value), "1900/01/01") -- Beginning of this quarter (Calendar)
=dateadd("q", datediff("q", "1900/01/01", Fields!RelativeDate.Value) + 1, "1900/01/01") -- Beginning of next quarter (Calendar)
=dateadd("q", datediff("q", "1900/01/01", Fields!RelativeDate.Value) - 1, "1900/01/01") -- Beginning of previous quarter (Calendar)
=dateadd("yyyy", datediff("yyyy", "1900/01/01", Fields!RelativeDate.Value), "1900/01/01") -- Beginning of this year
=dateadd("yyyy", datediff("yyyy", "1900/01/01", Fields!RelativeDate.Value) + 1, "1900/01/01") -- Beginning of next year
=dateadd("yyyy", datediff("yyyy", "1900/01/01", Fields!RelativeDate.Value) - 1, "1900/01/01") -- Beginning of previous year
Fitz
May 17, 2012 at 8:34 am
DateSerial(Year(Today)-1,12,31)
Substitute "Today" with date parameter or date Field from dataset
Far away is close at hand in the images of elsewhere.
Anon.
May 17, 2012 at 8:52 am
hi,
select dateadd(yy,datediff(yy,-1,@date),-1) is giving me last day of current year.
i want last day of previous month.
anyways got the answer
May 17, 2012 at 9:01 am
riya_dave (5/17/2012)
hi,select dateadd(yy,datediff(yy,-1,@date),-1) is giving me last day of current year.
i want last day of previous month.
anyways got the answer
Please be clear about your requirement as it does not seem as if you know. You had previously asked for a SSRS expression. The formula you have just posted is only working on years (yy) and does not mention months at all. Do you even read the links others give you, or do you need to be spoon-fed at all times?
select dateadd(mm,datediff(mm,0,@date),0) -- this gives the FIRST day this month
select dateadd(dd,-1,@date) -- gives the previous day
so ......
select dateadd(dd,-1,dateadd(mm,datediff(mm,0,@date),0)) -- gives the LAST day of the previous month.
Fitz
May 17, 2012 at 2:01 pm
riya_dave (5/16/2012)
hi,i want last day last year,what is the function for that.
example if i put date 02/29/12 the i should get 12/31/11.
if 03/31/2011 then it should be 12/31/2010
riya_dave (5/17/2012)
hi,select dateadd(yy,datediff(yy,-1,@date),-1) is giving me last day of current year.
i want last day of previous month.
anyways got the answer
last day of the previous month is not what you asked for originally. my MindReadAPI requires a future version of DBCC TimeWarp
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 17, 2012 at 2:15 pm
i want last day pf previous year,formula i correct ,its working in sql,
but i want to put same in ssrs expression
May 17, 2012 at 2:17 pm
riya_dave (5/17/2012)
i want last day pf previous year,formula i correct ,its working in sql,but i want to put same in ssrs expression
Have you looked up a few posts in this thread?
May 17, 2012 at 2:34 pm
Lynn Pettis (5/17/2012)
riya_dave (5/17/2012)
i want last day pf previous year,formula i correct ,its working in sql,but i want to put same in ssrs expression
Have you looked up a few posts in this thread?
No one can say I didn't try Lynn. I give up now, bye bye. He won't read this anyway.
Fitz
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply