beginndate and enddate as parameters

ich want to report inforamtions between one begindate and enddate.the
begin and the enddate can i give as Parameters.In the ReportParameters
Tool i have indicated this 2 Parameters as DateTime typ and in the SQl
Query i wrot but i have got wrong Results.i have wrote the sql Query as
follows:
SELECT Format(TimeLogged, 'dd/mm/yyyy hh:nn') AS TimeLogged,
OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
FROM MailTraffic
WHERE TimeLogged >= @.startdate AND TimeLogged < @.enddate
GROUP BY OriginatorAddress, RecipientAddress, TimeLogged;
Can you help me ?Format is not a native SQL function, besides you will need to use a HAVING
clause instead of WHERE.
Here is the final query:
SELECT CONVERT(varchar(32), TimeLogged, 120) AS TimeLogged,
OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
FROM MailTraffic
GROUP BY OriginatorAddress, RecipientAddress, TimeLogged
HAVING TimeLogged >= @.startdate AND TimeLogged < @.enddate
If you don't like the yyyy-mm-dd hh:nn:ss format (120) you can use another
one. Read BOL for further information about CAST and CONVERT functions.
Regards.
<khalderon1@.yahoo.de> escribió en el mensaje
news:1115976284.163984.212370@.o13g2000cwo.googlegroups.com...
> ich want to report inforamtions between one begindate and enddate.the
> begin and the enddate can i give as Parameters.In the ReportParameters
> Tool i have indicated this 2 Parameters as DateTime typ and in the SQl
> Query i wrot but i have got wrong Results.i have wrote the sql Query as
> follows:
> SELECT Format(TimeLogged, 'dd/mm/yyyy hh:nn') AS TimeLogged,
> OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
> COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
> FROM MailTraffic
> WHERE TimeLogged >= @.startdate AND TimeLogged < @.enddate
> GROUP BY OriginatorAddress, RecipientAddress, TimeLogged;
> Can you help me ?
>|||David,
Why should she convert the Timelogged to a varchar when RS can assign a
datatype of DateTime for a parameter? Does it have something to do with the
use of Format() in the original question? I'm not a VB person, so any
insight you can give me on this would be helpful.
Thanks,
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"David Lightman Robles" wrote:
> Format is not a native SQL function, besides you will need to use a HAVING
> clause instead of WHERE.
> Here is the final query:
> SELECT CONVERT(varchar(32), TimeLogged, 120) AS TimeLogged,
> OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
> COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
> FROM MailTraffic
> GROUP BY OriginatorAddress, RecipientAddress, TimeLogged
> HAVING TimeLogged >= @.startdate AND TimeLogged < @.enddate
> If you don't like the yyyy-mm-dd hh:nn:ss format (120) you can use another
> one. Read BOL for further information about CAST and CONVERT functions.
> Regards.
>
> <khalderon1@.yahoo.de> escribió en el mensaje
> news:1115976284.163984.212370@.o13g2000cwo.googlegroups.com...
> > ich want to report inforamtions between one begindate and enddate.the
> > begin and the enddate can i give as Parameters.In the ReportParameters
> > Tool i have indicated this 2 Parameters as DateTime typ and in the SQl
> > Query i wrot but i have got wrong Results.i have wrote the sql Query as
> > follows:
> >
> > SELECT Format(TimeLogged, 'dd/mm/yyyy hh:nn') AS TimeLogged,
> > OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
> > COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
> > FROM MailTraffic
> > WHERE TimeLogged >= @.startdate AND TimeLogged < @.enddate
> > GROUP BY OriginatorAddress, RecipientAddress, TimeLogged;
> >
> > Can you help me ?
> >
>
>|||Yes, you are right, I just used the CONVERT because in the original query
khalderon1 did it too. It really does not make much sense to me because, as
you say, RS can manage the datetime datatype perfectly and make the
formatting accordingly to the local settings of the user.
Regards.
"Catadmin" <goldpetalgraphics@.yahoo.com> escribió en el mensaje
news:F16A2F77-9721-4D1D-ACBB-FAE3AB74784F@.microsoft.com...
> David,
> Why should she convert the Timelogged to a varchar when RS can assign a
> datatype of DateTime for a parameter? Does it have something to do with
> the
> use of Format() in the original question? I'm not a VB person, so any
> insight you can give me on this would be helpful.
> Thanks,
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)
>
> "David Lightman Robles" wrote:
>> Format is not a native SQL function, besides you will need to use a
>> HAVING
>> clause instead of WHERE.
>> Here is the final query:
>> SELECT CONVERT(varchar(32), TimeLogged, 120) AS TimeLogged,
>> OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
>> COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
>> FROM MailTraffic
>> GROUP BY OriginatorAddress, RecipientAddress, TimeLogged
>> HAVING TimeLogged >= @.startdate AND TimeLogged < @.enddate
>> If you don't like the yyyy-mm-dd hh:nn:ss format (120) you can use
>> another
>> one. Read BOL for further information about CAST and CONVERT functions.
>> Regards.
>>
>> <khalderon1@.yahoo.de> escribió en el mensaje
>> news:1115976284.163984.212370@.o13g2000cwo.googlegroups.com...
>> > ich want to report inforamtions between one begindate and enddate.the
>> > begin and the enddate can i give as Parameters.In the ReportParameters
>> > Tool i have indicated this 2 Parameters as DateTime typ and in the SQl
>> > Query i wrot but i have got wrong Results.i have wrote the sql Query as
>> > follows:
>> >
>> > SELECT Format(TimeLogged, 'dd/mm/yyyy hh:nn') AS TimeLogged,
>> > OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
>> > COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
>> > FROM MailTraffic
>> > WHERE TimeLogged >= @.startdate AND TimeLogged < @.enddate
>> > GROUP BY OriginatorAddress, RecipientAddress, TimeLogged;
>> >
>> > Can you help me ?
>> >
>>|||Ah, Okay. I was getting seriously confused here. @.=)
Thank you for the clarification.
Catadmin
"David Lightman Robles" wrote:
> Yes, you are right, I just used the CONVERT because in the original query
> khalderon1 did it too. It really does not make much sense to me because, as
> you say, RS can manage the datetime datatype perfectly and make the
> formatting accordingly to the local settings of the user.
> Regards.
> "Catadmin" <goldpetalgraphics@.yahoo.com> escribió en el mensaje
> news:F16A2F77-9721-4D1D-ACBB-FAE3AB74784F@.microsoft.com...
> > David,
> >
> > Why should she convert the Timelogged to a varchar when RS can assign a
> > datatype of DateTime for a parameter? Does it have something to do with
> > the
> > use of Format() in the original question? I'm not a VB person, so any
> > insight you can give me on this would be helpful.
> >
> > Thanks,
> >
> > Catadmin
> > --
> > MCDBA, MCSA
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the
> > back?
> > @.=)
> >
> >
> > "David Lightman Robles" wrote:
> >
> >> Format is not a native SQL function, besides you will need to use a
> >> HAVING
> >> clause instead of WHERE.
> >>
> >> Here is the final query:
> >>
> >> SELECT CONVERT(varchar(32), TimeLogged, 120) AS TimeLogged,
> >> OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
> >> COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
> >> FROM MailTraffic
> >> GROUP BY OriginatorAddress, RecipientAddress, TimeLogged
> >> HAVING TimeLogged >= @.startdate AND TimeLogged < @.enddate
> >>
> >> If you don't like the yyyy-mm-dd hh:nn:ss format (120) you can use
> >> another
> >> one. Read BOL for further information about CAST and CONVERT functions.
> >>
> >> Regards.
> >>
> >>
> >> <khalderon1@.yahoo.de> escribió en el mensaje
> >> news:1115976284.163984.212370@.o13g2000cwo.googlegroups.com...
> >> > ich want to report inforamtions between one begindate and enddate.the
> >> > begin and the enddate can i give as Parameters.In the ReportParameters
> >> > Tool i have indicated this 2 Parameters as DateTime typ and in the SQl
> >> > Query i wrot but i have got wrong Results.i have wrote the sql Query as
> >> > follows:
> >> >
> >> > SELECT Format(TimeLogged, 'dd/mm/yyyy hh:nn') AS TimeLogged,
> >> > OriginatorAddress, RecipientAddress, SUM([Size]) AS TotalTrafficVolume,
> >> > COUNT(*) AS TotalItems, AVG(DeliveryTime) AS AvgDeliveryTime
> >> > FROM MailTraffic
> >> > WHERE TimeLogged >= @.startdate AND TimeLogged < @.enddate
> >> > GROUP BY OriginatorAddress, RecipientAddress, TimeLogged;
> >> >
> >> > Can you help me ?
> >> >
> >>
> >>
> >>
>
>