CurrentDate within a report?

Hi all, im having difficulty using dates within my report. Any advice
welcome.
Basically I want to restrict my query by date, however, I want my start and
enddate parameters to have universal generic values.
I.e.
startdate = CurrentDate - 31
enddate = CurrentDate
I cannot figure out how todo this within RS 2005, I have attempted to use
some of the .NET datetime functions but I cannot get them to work (i.e.
"Today() - 31" etc)
What should I be doing?
Kind regards
TazTry the following expressions:
=Today() -- for StartDate
=DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
HTH
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Hi all, im having difficulty using dates within my report. Any advice
> welcome.
> Basically I want to restrict my query by date, however, I want my start and
> enddate parameters to have universal generic values.
> I.e.
> startdate = CurrentDate - 31
> enddate = CurrentDate
> I cannot figure out how todo this within RS 2005, I have attempted to use
> some of the .NET datetime functions but I cannot get them to work (i.e.
> "Today() - 31" etc)
> What should I be doing?
> Kind regards
> Taz
>
>|||Many thanks for the reply, this has helped alot.
However, a new problem has surfaced. There is an inconsistency between the
date format in my data fields and the format within the database. I need to
force an American format, how can I do this?
Alternatively, is there a way to convert UK dates into American dates?
Currently, each time I run my report, within the parameter window, the
values are being alternated (which is VERY strange).
I.e.
12/01/2006 -> Press "View Report"
01/12/2006 -> Press "View Report"
12/01/2006 -> Press "View Report"
etc etc
Thanks for your help
Taz
"magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> Try the following expressions:
> =Today() -- for StartDate
> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> HTH
> --
> Magendo_man
> Freelance SQL Reporting Services developer
> Stirling, Scotland
>
> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>|||did you put that date formula into an expression or did you put it in the
data tab.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need
> to force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>|||I have put those formulas into the Default values within the "Report
Parameters" setup.
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||try this: =switch(Fields!YOURFIELD.Value = "US","M/d/yy")
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
> did you put that date formula into an expression or did you put it in the
> data tab.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my start
>> and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>|||I'm not sure where you need to force an American date format.
If you are referencing columns in your SQL Server database which have a
datatype of DATETIME then you shouldn't have a problem. If you are running
RS2000 and haven't applied Service Pack two you can get inconsistencies in
presentation of dates when entering report parameters - apply the service
pack.
Also, you can force a UK format in your reports by using the format property
of a given textbox, making it something like dd-MM-yy.
If none of these pointers helps you then please clarify what the exact
problem is with date formats.
--
Magendo_man
Freelance SQL Reporting Services developer
Stirling, Scotland
"Tarun Mistry" wrote:
> Many thanks for the reply, this has helped alot.
> However, a new problem has surfaced. There is an inconsistency between the
> date format in my data fields and the format within the database. I need to
> force an American format, how can I do this?
> Alternatively, is there a way to convert UK dates into American dates?
> Currently, each time I run my report, within the parameter window, the
> values are being alternated (which is VERY strange).
> I.e.
> 12/01/2006 -> Press "View Report"
> 01/12/2006 -> Press "View Report"
> 12/01/2006 -> Press "View Report"
> etc etc
> Thanks for your help
> Taz
> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
> > Try the following expressions:
> >
> > =Today() -- for StartDate
> >
> > =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
> >
> > HTH
> >
> > --
> > Magendo_man
> >
> > Freelance SQL Reporting Services developer
> > Stirling, Scotland
> >
> >
> > "Tarun Mistry" wrote:
> >
> >> Hi all, im having difficulty using dates within my report. Any advice
> >> welcome.
> >>
> >> Basically I want to restrict my query by date, however, I want my start
> >> and
> >> enddate parameters to have universal generic values.
> >>
> >> I.e.
> >>
> >> startdate = CurrentDate - 31
> >> enddate = CurrentDate
> >>
> >> I cannot figure out how todo this within RS 2005, I have attempted to use
> >> some of the .NET datetime functions but I cannot get them to work (i.e.
> >> "Today() - 31" etc)
> >>
> >> What should I be doing?
> >>
> >> Kind regards
> >> Taz
> >>
> >>
> >>
>
>|||and if my last post doesnt help then try changing up your formula some.
=dateadd("d",-31,today). leave off the () and use "d" instead of
dateinterval.day. I have never had my dates go crazy like that.
"Tarun Mistry" <nospam@.nospam.com> wrote in message
news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>I have put those formulas into the Default values within the "Report
>Parameters" setup.
> Taz
> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in the
>> data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>|||Guys,
I really don't understand what was going on. The "flipping" date issue was
only happening within RS and not in the webservice. It seems to be fine now.
Thanks for all the help and the magic formulaes!!
Taz
"Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
news:OdnS4Ko0GHA.3752@.TK2MSFTNGP02.phx.gbl...
> and if my last post doesnt help then try changing up your formula some.
> =dateadd("d",-31,today). leave off the () and use "d" instead of
> dateinterval.day. I have never had my dates go crazy like that.
> "Tarun Mistry" <nospam@.nospam.com> wrote in message
> news:eE1DXHo0GHA.4972@.TK2MSFTNGP03.phx.gbl...
>>I have put those formulas into the Default values within the "Report
>>Parameters" setup.
>> Taz
>> "Ben Watts" <ben.watts@.aaronnickellhomes.com> wrote in message
>> news:eE5m$8n0GHA.1288@.TK2MSFTNGP03.phx.gbl...
>> did you put that date formula into an expression or did you put it in
>> the data tab.
>> "Tarun Mistry" <nospam@.nospam.com> wrote in message
>> news:ezuOx5n0GHA.1568@.TK2MSFTNGP03.phx.gbl...
>> Many thanks for the reply, this has helped alot.
>> However, a new problem has surfaced. There is an inconsistency between
>> the date format in my data fields and the format within the database. I
>> need to force an American format, how can I do this?
>> Alternatively, is there a way to convert UK dates into American dates?
>> Currently, each time I run my report, within the parameter window, the
>> values are being alternated (which is VERY strange).
>> I.e.
>> 12/01/2006 -> Press "View Report"
>> 01/12/2006 -> Press "View Report"
>> 12/01/2006 -> Press "View Report"
>> etc etc
>> Thanks for your help
>> Taz
>> "magendo_man" <sql@.kappa.co.uk.(donotspam)> wrote in message
>> news:BFD2C7D1-0EF2-45F2-81AE-57504A839891@.microsoft.com...
>> Try the following expressions:
>> =Today() -- for StartDate
>> =DateAdd(DateInterval.Day, -31, Today()) -- for EndDate
>> HTH
>> --
>> Magendo_man
>> Freelance SQL Reporting Services developer
>> Stirling, Scotland
>>
>> "Tarun Mistry" wrote:
>> Hi all, im having difficulty using dates within my report. Any advice
>> welcome.
>> Basically I want to restrict my query by date, however, I want my
>> start and
>> enddate parameters to have universal generic values.
>> I.e.
>> startdate = CurrentDate - 31
>> enddate = CurrentDate
>> I cannot figure out how todo this within RS 2005, I have attempted to
>> use
>> some of the .NET datetime functions but I cannot get them to work
>> (i.e.
>> "Today() - 31" etc)
>> What should I be doing?
>> Kind regards
>> Taz
>>
>>
>>
>>
>|||It seems i posted in success a little too early,
My default parameter is correctly appearing in UK format, however when I
pass it to another report, it is being sent in american format, or being
interpretted in american format. The chart that sends the data is set to UK,
as is the main report.
what could be afoot here? furter, my reports may be deployed on a number of
different systems, will this behaviour happen differently on different
servers depending on its language setting'
Thanks
Taz|||Tarun Mistry wrote:
> It seems i posted in success a little too early,
> My default parameter is correctly appearing in UK format, however when I
> pass it to another report, it is being sent in american format, or being
> interpretted in american format. The chart that sends the data is set to UK,
> as is the main report.
> what could be afoot here? furter, my reports may be deployed on a number of
> different systems, will this behaviour happen differently on different
> servers depending on its language setting'
> Thanks
> Taz
If you are running SQL 2005 make sure you have installed servicepack 1.
The thing you are describing looks a bit like this post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1|||> If you are running SQL 2005 make sure you have installed servicepack 1.
> The thing you are describing looks a bit like this post.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255248&SiteID=1&PageID=1
>
Many thanks Peter, it does indeed look like this is the problem.
I googled the issue but couldn't find anything, i really appreciate your
help.
Downloading SP1 now, fingers crossed.
Kind regards
Taz