Any ideas on this one ?

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 !
JackI 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
Jack Nielsen wrote:
> 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|||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
> 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
> Jack Nielsen wrote:
> > 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
>|||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
"Jack Nielsen" wrote:
> 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
> > 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
> >
> > Jack Nielsen wrote:
> >
> > > 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
> >
>
>|||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
ducky wrote:
> 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
>
> "Jack Nielsen" wrote:
> > 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
> >
> > > 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
> > >
> > > Jack Nielsen wrote:
> > >
> > > > 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
> > >
> >
> >
> >|||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).
"Chris McGuigan" <chris.mcguigan@.zycko.com> wrote in message
news:eP03NtUiFHA.2180@.TK2MSFTNGP15.phx.gbl...
> 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
>
> ducky wrote:
>> 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
>>
>> "Jack Nielsen" wrote:
>> > 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
>> >
>> > > 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
>> > >
>> > > Jack Nielsen wrote:
>> > >
>> > > > 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
>> > >
>> >
>> >
>> >
>|||> 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).
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