sql server reporting services: Any ideas on this one ? -- sql server reporting services

Re: Any ideas on this one ?

Chris McGuigan
7/15/2005 3:22:41 AM

sql server reporting services:

I ended up writing a sql function to do this.
Basically the function subtracts the hours , minutes and seconds using
the DateAdd statement. There vb equivalents to this to - look up date
functions in BOL.

Chris

[quoted text, click to view]

Re: Any ideas on this one ?

ducky
7/15/2005 7:18:03 AM

Can you not just return the datetime from the dataset and format it in the
layout of the report using an expression (=Format(Fields!dateTimeField.Value,
"MMM dd, yyyy"))? Am I missing something in your requirements?

MKD

[quoted text, click to view]

Re: Any ideas on this one ?

Chris McGuigan
7/15/2005 7:41:35 AM

Ducky,
This is a date from a dataset, being used to populate a parameter list.
This is why we have to be a bit more convaluted.

Jack,
In your dataset do something like this;
Select MyDate, dbo.udf_MyDate(MyDate, '/') As Label From etc ...
In your parameter set MyDate as the Value and Label as the Label.

Chris

[quoted text, click to view]

Re: Any ideas on this one ?

goodman93
7/15/2005 11:59:20 AM

If your RS parameter is set to a string, the original
"convert(datetime,period,105)" should give you the listing you desire. When
this parameter is then passed to SQL it "should" automatically be recognized
as a date, but if not, you could pass the parameter as as string, and then
declare and set a new SQL parameter to the cast(@param as datetime).

[quoted text, click to view]

Any ideas on this one ?

Jack Nielsen
7/15/2005 12:10:51 PM

I have a Datetime parameter from a dataset. It also shows the time but I
only need the date, is it possible somehow to change how it displays the
parameter. As what I can see it is only possible to choose datetime format.
I tried to convert it like this convert(datetime,period,105) as dateonly and
then use this field but it shows exactly the same !

Jack

Re: Any ideas on this one ?

Jack Nielsen
7/15/2005 1:38:04 PM

Could this be used somehow, and if how do I show this new field in the
Parameter and still use the datetime field in the sql statement ?

USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE id =
object_id(N'[dbo].[udf_MyDate]') and xtype = N'FN')
DROP FUNCTION [dbo].[udf_MyDate]
GO
CREATE FUNCTION udf_MyDate (@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(dd, @indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(yy, @indate))
END
GO

This is the statement where i include the parameter
HAVING (DEBSTAT.DATASET = @Regnskab) AND (DEBSTAT.PERIODESTART >
@periodestartparm) AND (DEBSTAT.PERIODESTART < @periodeslutparm)

And it now shows like this 12/01/2005 00:00:00 in the report parameter,
would like it only to show 12/01/2005 but still using a real datetime format
so that the statement still works.

Jack

[quoted text, click to view]

Re: Any ideas on this one ?

Jack Nielsen
7/18/2005 12:12:14 PM

[quoted text, click to view]

This doesn't seem to work, if I set it to string and do the convert it still
shows the time.

I'm trying to do it the other way around but get syntax error, what could be
wrong here:

CREATE FUNCTION udf_MyDate (@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(dd, @indate))
+ @separator
+ CONVERT(Nvarchar(20), datepart(yy, @indate))
END
GO

SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1, [dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART

DROP FUNCTION [dbo].[udf_MyDate]

Jack

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.