anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.
|||it works! thanks
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it i
s
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it i
s
Null in all 3 tables I will just use 0 for the value. So use maxi from tabl
e
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
****************************************
**
pri key * color_id * size_id * date* maxi *
****************************************
**
* 1 * 2 * 5 * 2/1/07* 7 *
****************************************
**
* 2 * 4 * 6 * 2/2/07* NULL *
****************************************
**
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:

> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day fr
om
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if n
ot
> null, then if null use value from table c for maxi.
> tableA
> ****************************************
**
> pri key * color_id * size_id * date* maxi *
> ****************************************
**
> * 1 * 2 * 5 * 2/1/07* 7 *
> ****************************************
**
> * 2 * 4 * 6 * 2/2/07* NULL *
> ****************************************
**
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

anyone good with queries

Hi below is a simplified database table setup that I have but basically it is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it is
Null in all 3 tables I will just use 0 for the value. So use maxi from table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.Try:
select
coalesce (a.maxi, b.maxi, c.maxi, 0)
from
table A a
left join
tableB b on b.[pri key] = a.[pri key]
left join
tableC c on c.[pri key] = a.[pri key]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
Hi below is a simplified database table setup that I have but basically it
is
3 tables that are joined and one table has a date in it. Also in all 3
tables is a value maxi and I need to retrieve 1 maxi value for each day from
the query. If the value is NULL in table A I want to get the value from
table B and if it is null in table B I want to get it from table C. If it
is
Null in all 3 tables I will just use 0 for the value. So use maxi from
table
A if not null, but if null check table b and use maxi from that table if not
null, then if null use value from table c for maxi.
tableA
******************************************
pri key * color_id * size_id * date* maxi *
******************************************
* 1 * 2 * 5 * 2/1/07* 7 *
******************************************
* 2 * 4 * 6 * 2/2/07* NULL *
******************************************
tableB
****************************
* color id * color name * maxi *
****************************
* 2 * blue * 6 *
****************************
* 4 * red * NULL *
***************************
tableC
****************************
* size id * color name * maxi *
****************************
* 5 * small * 6 *
****************************
* 6 * medium * 10 *
***************************
so a query for a date range of 2/1/07 to 2/2/07 would return
2 records and maxi would be 7 for 2/1/07 from table A and 10 from
table C for 2/2/07.
Thanks
--
Paul G
Software engineer.|||it works! thanks
--
Paul G
Software engineer.
"Tom Moreau" wrote:
> Try:
> select
> coalesce (a.maxi, b.maxi, c.maxi, 0)
> from
> table A a
> left join
> tableB b on b.[pri key] = a.[pri key]
> left join
> tableC c on c.[pri key] = a.[pri key]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:41785FDC-FBEF-4A50-863F-6C96A51ABE76@.microsoft.com...
> Hi below is a simplified database table setup that I have but basically it
> is
> 3 tables that are joined and one table has a date in it. Also in all 3
> tables is a value maxi and I need to retrieve 1 maxi value for each day from
> the query. If the value is NULL in table A I want to get the value from
> table B and if it is null in table B I want to get it from table C. If it
> is
> Null in all 3 tables I will just use 0 for the value. So use maxi from
> table
> A if not null, but if null check table b and use maxi from that table if not
> null, then if null use value from table c for maxi.
> tableA
> ******************************************
> pri key * color_id * size_id * date* maxi *
> ******************************************
> * 1 * 2 * 5 * 2/1/07* 7 *
> ******************************************
> * 2 * 4 * 6 * 2/2/07* NULL *
> ******************************************
> tableB
> ****************************
> * color id * color name * maxi *
> ****************************
> * 2 * blue * 6 *
> ****************************
> * 4 * red * NULL *
> ***************************
> tableC
> ****************************
> * size id * color name * maxi *
> ****************************
> * 5 * small * 6 *
> ****************************
> * 6 * medium * 10 *
> ***************************
> so a query for a date range of 2/1/07 to 2/2/07 would return
> 2 records and maxi would be 7 for 2/1/07 from table A and 10 from
> table C for 2/2/07.
> Thanks
> --
> Paul G
> Software engineer.
>

Anybody knows the problem of this code?

My code is this:
shared function dataFisica( byVal data as Date ) as Date
If data='01/01/1900' Then
return ' '
Else
return data
End If
end function
And the reporting services do an error in line 1.
Try using double quotes instead of single quotes

function dataFisica( byVal data as Date ) as Date
If data="01/01/1900" Then
return ""
Else
return data
End If
end function

any way to check for out of date stats or indexes

Is there any way to check for stats or indexes being out of date ? Using SQL
server 2000STATS_DATE
Returns the date that the statistics for the specified index were last
updated.
Syntax
STATS_DATE ( table_id , index_id )
Also DBCC SHOW_STATISTICS ( table , target )
See BOL for details
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O8pAMwRcDHA.616@.TK2MSFTNGP11.phx.gbl...
Is there any way to check for stats or indexes being out of date ? Using SQL
server 2000

Any significant differences between these two date CAST statements

I need to find records that have a date stamp of yesterday. I have created two where clauses that use CAST and DATETIME. Both seem to return the same results. Is either one better?

CAST(FLOOR(CAST(cc.Date AS float)) AS datetime) = CAST(FLOOR(CAST((getdate()-1) AS float)) AS datetime)

-- OR --

cast (round(cast(cc.Date as float),0,1) as datetime) = cast (round(cast((getdate()-1) as float),0,1) as datetime)

BTW: Oracle handles this easily as trunc(mydate)

Thanks in advance

Oh Using SQLServer 2005

Doug

www.cooltimbers.com

It looks to me like they will both work:

declare @.morningDt datetime set @.morningDt = '3/13/7 0:05'
declare @.afternoonDt datetime set @.afternoonDt = '3/13/7 23:59'

select cast(round(cast(@.morningDt as float),0,1) as datetime),
cast(round(cast(@.afternoonDt as float),0,1) as datetime),
round(cast(@.morningDt as float),0,1) ,
round(cast(@.afternoonDt as float),0,1)

-- -- - -
2007-03-13 00:00:00.000 2007-03-13 00:00:00.000 39152.0 39152.0

I am more used to seeing the FLOOR version.

|||

Thanks Kent for the prompt reply... and for your help in general!

I sure do wish Microsoft implements something similar to Oracles "TRUNC" function that nicely truncates to a day.

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