Date Range Question..

I have a parameter called @.To that utilizes the date field in my datasource,
What I need it to do is to give the data that ends at @.To and that starts at
the first day of the month specified in the parameter @.To.
So I have constructed this query
WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
However this doesn't work :(
So I do it this way:
WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
This does work, however the parameter must be entered in the following
format Apr 16 2004 (which is useless to me)
Can anyone out there help?
AshYou might want to use an expression-based command text, where you write a
VB.NET expression which evaluates into a SQL query commandtext. However, you
have to be careful to not mix up SQL and VB.NET syntax. You might want to
checkout this walkthrough in BOL:
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
Using an expression-based command text you could then use VB functions like
CDate(), etc. to dynamically construct the dates you need in the query.
There are many options for your case. Assuming you have have two DateTime
report parameters:
="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
smalldatatime))"
You could then initialize these two report parameters with an
expression-based default value that automatically calculates the first and
the last day of the current month:
StartDate:
=DateValue(Month(Today) & " 1," & Year(Today))
EndDate:
=DateValue(Month(Today) & " 1," &
Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
Month(Today))-1)
Information about DateTime / conversion functions is available on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> I have a parameter called @.To that utilizes the date field in my
datasource,
> What I need it to do is to give the data that ends at @.To and that starts
at
> the first day of the month specified in the parameter @.To.
> So I have constructed this query
> WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> However this doesn't work :(
> So I do it this way:
> WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> This does work, however the parameter must be entered in the following
> format Apr 16 2004 (which is useless to me)
> Can anyone out there help?
> Ash
>|||Thanks for the quick reply!!!
I do not want to use two parameters only one. And sadly I just don't have
the knowledge of VB to construct these expressions. I am basically there
using SQL only code and will continue the battle.
Thanks for your help.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O6lS%23b1VEHA.3428@.TK2MSFTNGP12.phx.gbl...
> You might want to use an expression-based command text, where you write a
> VB.NET expression which evaluates into a SQL query commandtext. However,
you
> have to be careful to not mix up SQL and VB.NET syntax. You might want to
> checkout this walkthrough in BOL:
>
http://msdn.microsoft.com/library/en-us/RSAMPLES/htm/rss_tutorials_v1_7qr7.asp
> Using an expression-based command text you could then use VB functions
like
> CDate(), etc. to dynamically construct the dates you need in the query.
> There are many options for your case. Assuming you have have two DateTime
> report parameters:
> ="SELECT ... BETWEEN CAST('" & CDate(Parameters!StartDate.Value) & "' as
> smalldatetime) AND CAST('" & CDate(Parameters!EndDate.Value) & "' as
> smalldatatime))"
> You could then initialize these two report parameters with an
> expression-based default value that automatically calculates the first and
> the last day of the current month:
> StartDate:
> =DateValue(Month(Today) & " 1," & Year(Today))
> EndDate:
> =DateValue(Month(Today) & " 1," &
> Year(Today)).AddDays(System.DateTime.DaysInMonth(Year(Today),
> Month(Today))-1)
>
> Information about DateTime / conversion functions is available on MSDN:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimememberstopic.asp
> http://msdn.microsoft.com/library/en-us/vblr7/html/vafctDateAdd.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatediff.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctdatepart.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vagrptypeconversion.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "AshVsAOD" <.> wrote in message
> news:eAxsg90VEHA.2564@.TK2MSFTNGP11.phx.gbl...
> > I have a parameter called @.To that utilizes the date field in my
> datasource,
> > What I need it to do is to give the data that ends at @.To and that
starts
> at
> > the first day of the month specified in the parameter @.To.
> >
> > So I have constructed this query
> >
> > WHERE (CONVERT(char(11), Date, 3) BETWEEN '1/@.TO/@.TO' AND @.TO)
> >
> > However this doesn't work :(
> >
> > So I do it this way:
> > WHERE (CONVERT(char(11), Date) BETWEEN '@.TO 1 @.TO' AND @.TO)
> >
> > This does work, however the parameter must be entered in the following
> > format Apr 16 2004 (which is useless to me)
> >
> > Can anyone out there help?
> >
> > Ash
> >
> >
>