failed to search using d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007'

hi all programmers.
i want to find the record that created on only 05/07/2007
the datetime in my database is 5/7/2007 4:09:00 PM
i used this (d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007' ) inside the where clause.
but it returns no result.
please help me on this issue. thanks for all helps.

Try this

d.DateCreated >='05/07/2007' AND d.DateCreated< '06/07/2007'

|||

Or, to avoid ambiguity caused by your connection's language settings, you might want to use the following date format:

yyyy-mm-dd hh:mi: ss.mmm

<ignore the space after mi: - excluding it from forum posts results in this: mi >

e.g.

d.DateCreated >= '2007-07-05 00:00:00.000' AND d.DateCreated < '2007-07-06 00:00:00.000'

or

d.DateCreated >= '2007-05-07 00:00:00.000' AND d.DateCreated < '2007-05-08 00:00:00.000'

(depending on the format of the date that you oiginally provided)

Chris

|||try this one

CONVERT(smalldatetime,CONVERT(varchar(10),d.DateCreated,101)) >=CONVERT(smalldatetime,'05/07/2007') AND
CONVERT(smalldatetime,CONVERT(varchar(10), d.DateCreated,101))<= CONVERT(smalldatetime,'05/07/2007')|||The where clause you specified is only looking for EXACTLY midnight, 5/07/2007 12:00am. If you want to search for anything on 5/7 use:

DateCreated >='5/7/2007' AND DateCreated < '5/8/2007'

failed to convert parameter value from DateTime to a Int32

I have criteria in my dataset that throws the above error. Iâ'm just trying
to get RS to understand the parsing out of a DateTime Field the values of a
month or year. I know the value I am comparing to is a number. Iâ've tried
the following different ways:
= YEAR(@.Date_Selection) â' 1
= DatePart(yy,@.Date_Selection) - 1
Additionally, Iâ've tried casting and converting back and forth from and to
VarChar and Int to no avail.
Any thoughts?
--
Marty ClineIt would be easier to help you if we could see the field types of your table
and the whole query that's included in your SSRS dataset (including the
parameter).
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>I have criteria in my dataset that throws the above error. Iâ'm just trying
> to get RS to understand the parsing out of a DateTime Field the values of
> a
> month or year. I know the value I am comparing to is a number. Iâ've tried
> the following different ways:
> = YEAR(@.Date_Selection) â' 1
> = DatePart(yy,@.Date_Selection) - 1
> Additionally, Iâ've tried casting and converting back and forth from and to
> VarChar and Int to no avail.
> Any thoughts?
> --
> Marty Cline|||Sure, butt here's the real "kicker".
This work perfectly in SQL server studio. Reporting services just has
issues with the datetime parameter.
Here's the sql:
DECLARE @.DATE_Selection AS DATETIME
SET @.DATE_Selection = '10/31/2007'
SELECT
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD =td.Curr_hold INNER JOIN
Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
(ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
(1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection) -
1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||nevermind. It just started working. I have no idea why.
--
Marty Cline
"Alain Quesnel" wrote:
> It would be easier to help you if we could see the field types of your table
> and the whole query that's included in your SSRS dataset (including the
> parameter).
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >I have criteria in my dataset that throws the above error. Iâ'm just trying
> > to get RS to understand the parsing out of a DateTime Field the values of
> > a
> > month or year. I know the value I am comparing to is a number. Iâ've tried
> > the following different ways:
> >
> > = YEAR(@.Date_Selection) â' 1
> > = DatePart(yy,@.Date_Selection) - 1
> >
> > Additionally, Iâ've tried casting and converting back and forth from and to
> > VarChar and Int to no avail.
> >
> > Any thoughts?
> > --
> >
> > Marty Cline
>|||Did you set the proper type for your parameter in SSRS? It defaults to
string the first time you create it, and if you modify it afterwards, it
tends to revert back to string. From your SQL code, it should be datetime.
If that's not the issue, you could try this:
LEFT(convert(varchar, @.DATE_Selection, 2), 2)
instead of this:
CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
And so on for the rest of your fields. You never know...
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> Sure, butt here's the real "kicker".
> This work perfectly in SQL server studio. Reporting services just has
> issues with the datetime parameter.
> Here's the sql:
> DECLARE @.DATE_Selection AS DATETIME
> SET @.DATE_Selection = '10/31/2007'
> SELECT
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => td.Curr_hold INNER JOIN
> Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> (1 = 1) AND (ir.YEAR = DATEPART(yy,
> @.Date_Selection) -
> 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> <=> 20) AND
> (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> It would be easier to help you if we could see the field types of your
>> table
>> and the whole query that's included in your SSRS dataset (including the
>> parameter).
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >trying
>> > to get RS to understand the parsing out of a DateTime Field the values
>> > of
>> > a
>> > month or year. I know the value I am comparing to is a number. Iâ've
>> > tried
>> > the following different ways:
>> >
>> > = YEAR(@.Date_Selection) â' 1
>> > = DatePart(yy,@.Date_Selection) - 1
>> >
>> > Additionally, Iâ've tried casting and converting back and forth from and
>> > to
>> > VarChar and Int to no avail.
>> >
>> > Any thoughts?
>> > --
>> >
>> > Marty Cline
>>|||Thanks for your response. To answer your question, yes the parameter type
was set to DateTime.
I'm not sure why it was not working to begin with but it seems to be working
now. Probably something I did not know I did, but I'm not arguing. Thanks
for your help again!
--
Marty Cline
"Alain Quesnel" wrote:
> Did you set the proper type for your parameter in SSRS? It defaults to
> string the first time you create it, and if you modify it afterwards, it
> tends to revert back to string. From your SQL code, it should be datetime.
> If that's not the issue, you could try this:
> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
> instead of this:
> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
> And so on for the rest of your fields. You never know...
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
> > Sure, butt here's the real "kicker".
> >
> > This work perfectly in SQL server studio. Reporting services just has
> > issues with the datetime parameter.
> >
> > Here's the sql:
> >
> > DECLARE @.DATE_Selection AS DATETIME
> > SET @.DATE_Selection = '10/31/2007'
> >
> > SELECT
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
> >
> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
> >
> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD => > td.Curr_hold INNER JOIN
> > Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20)
> > AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
> > @.Date_Selection) -
> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL
> > <=> > 20) AND
> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
> >
> >
> > --
> >
> > Marty Cline
> >
> >
> > "Alain Quesnel" wrote:
> >
> >> It would be easier to help you if we could see the field types of your
> >> table
> >> and the whole query that's included in your SSRS dataset (including the
> >> parameter).
> >>
> >>
> >>
> >> Alain Quesnel
> >> alainsansspam@.logiquel.com
> >>
> >> www.logiquel.com
> >>
> >>
> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
> >> >I have criteria in my dataset that throws the above error. Iâ'm just
> >> >trying
> >> > to get RS to understand the parsing out of a DateTime Field the values
> >> > of
> >> > a
> >> > month or year. I know the value I am comparing to is a number. Iâ've
> >> > tried
> >> > the following different ways:
> >> >
> >> > = YEAR(@.Date_Selection) â' 1
> >> > = DatePart(yy,@.Date_Selection) - 1
> >> >
> >> > Additionally, Iâ've tried casting and converting back and forth from and
> >> > to
> >> > VarChar and Int to no avail.
> >> >
> >> > Any thoughts?
> >> > --
> >> >
> >> > Marty Cline
> >>
> >>
>|||You're welcome.
Debugging a report in SSRS based on the error message on the Preview page
isn't exactly intuitive. Or at least not in the beginning when you're not
familiar with the environment.
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
news:491E40DE-D931-4162-B5F5-C707533EAD34@.microsoft.com...
> Thanks for your response. To answer your question, yes the parameter type
> was set to DateTime.
> I'm not sure why it was not working to begin with but it seems to be
> working
> now. Probably something I did not know I did, but I'm not arguing.
> Thanks
> for your help again!
> --
> Marty Cline
>
> "Alain Quesnel" wrote:
>> Did you set the proper type for your parameter in SSRS? It defaults to
>> string the first time you create it, and if you modify it afterwards, it
>> tends to revert back to string. From your SQL code, it should be
>> datetime.
>> If that's not the issue, you could try this:
>> LEFT(convert(varchar, @.DATE_Selection, 2), 2)
>> instead of this:
>> CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR)
>> And so on for the rest of your fields. You never know...
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> news:F7CC4719-9007-4DFD-B5A5-D3C813067FAC@.microsoft.com...
>> > Sure, butt here's the real "kicker".
>> >
>> > This work perfectly in SQL server studio. Reporting services just has
>> > issues with the datetime parameter.
>> >
>> > Here's the sql:
>> >
>> > DECLARE @.DATE_Selection AS DATETIME
>> > SET @.DATE_Selection = '10/31/2007'
>> >
>> > SELECT
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) AS Start,
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
>> >
>> > CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
>> > CAST(CAST(RIGHT(DATEPART(YY,@.DATE_Selection),2) AS VarCHAR) +
>> > CAST(DATEPART(MM,@.DATE_Selection) AS VarCHAR)AS INT) -
>> > CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
>> > LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS
>> > ReportOrder,
>> >
>> > ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month,
>> > DATENAME(d,
>> > ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
>> > td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL,
>> > ir.IMP_10_CNT,
>> > ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
>> > ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
>> > sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
>> > FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
>> > Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD
>> > =>> > td.Curr_hold INNER JOIN
>> > Investments.CAM_SEC_DIM AS sd ON td.Cusip =>> > sd.CUSIP
>> > WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @.Date_Selection)) AND
>> > (ir.MONTH < DATEPART(mm, @.Date_Selection)) AND (ir.IMPAIRMENT_LVL <=>> > 20)
>> > AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > OR
>> > (1 = 1) AND (ir.YEAR = DATEPART(yy,
>> > @.Date_Selection) -
>> > 1) AND (ir.MONTH >= DATEPART(mm, @.Date_Selection)) AND
>> > (ir.IMPAIRMENT_LVL
>> > <=>> > 20) AND
>> > (ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
>> > ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
>> >
>> >
>> > --
>> >
>> > Marty Cline
>> >
>> >
>> > "Alain Quesnel" wrote:
>> >
>> >> It would be easier to help you if we could see the field types of your
>> >> table
>> >> and the whole query that's included in your SSRS dataset (including
>> >> the
>> >> parameter).
>> >>
>> >>
>> >>
>> >> Alain Quesnel
>> >> alainsansspam@.logiquel.com
>> >>
>> >> www.logiquel.com
>> >>
>> >>
>> >> "Marty Cline" <MartyCline@.discussions.microsoft.com> wrote in message
>> >> news:3B69E102-C30E-4FE9-AB33-B951921D26C0@.microsoft.com...
>> >> >I have criteria in my dataset that throws the above error. Iâ'm just
>> >> >trying
>> >> > to get RS to understand the parsing out of a DateTime Field the
>> >> > values
>> >> > of
>> >> > a
>> >> > month or year. I know the value I am comparing to is a number. Iâ've
>> >> > tried
>> >> > the following different ways:
>> >> >
>> >> > = YEAR(@.Date_Selection) â' 1
>> >> > = DatePart(yy,@.Date_Selection) - 1
>> >> >
>> >> > Additionally, Iâ've tried casting and converting back and forth from
>> >> > and
>> >> > to
>> >> > VarChar and Int to no avail.
>> >> >
>> >> > Any thoughts?
>> >> > --
>> >> >
>> >> > Marty Cline
>> >>
>> >>
>>

Extremely Slow Table

Hi,

I have a table defined as
CREATE TABLE [SH_Data] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[TroubleshootId] [int] NOT NULL ,
[ReasonID] [int] NULL ,
[reason_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[maj_reason_id] [int] NULL ,
[maj_reason_desc] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[ActionID] [int] NULL ,
[action_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[WinningCaseTitle] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[Duration] [int] NULL ,
[dm_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[ConnectMethod] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[dm_motive] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[HnWhichWlan] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[RouterUsedToConnect] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[OS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[WinXpSp2Installed] [nvarchar] (255) COLLATE
SQL_Latin1_General_CP1_CS_AS NULL ,
[Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[Login] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
,
[EnteredBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS
NULL ,
[Acct_Num] [int] NULL ,
[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
CONSTRAINT [PK_SH_Data] PRIMARY KEY CLUSTERED
(
[TroubleshootId]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Which contains 5.6 Million rows and has non clustered indexes on Date,
ReasonID, maj_Reason, Connection. Compared to other tables on the same
server this one is extremely slow. A simple query such as :

SELECT
SD.reason_desc,
SD.Duration,
SD.maj_reason_desc,
SD.[Connection],
SD.aolEnteredBy

FROM dbo.[Sherlock Data] SD

Where SD.[Date] > Dateadd(Month,-2,Getdate())

takes over 2 minutes to run ! I realise the table contains several
large columns which make the table quite large but unfortunately this
cannot be changed for the moment.

How can i assess what is causing the length of Query time ? And what
could i possibly do to speed this table up ? The database itself is
running on a dedicated server which has some other databases. None of
which have this performance issue.

Anyone have any ideas ?Do other queries which benefit of indexes also have bad performance ?

Where SD.[Date] > Dateadd(Month,-2,Getdate())

does a row based comparison, not using the indexes or what does the
query plan tells you about it ?

(Markt the query in QA and press CTRL+L) to see it.

Jens Suessmeyer.|||One possiblility is to change the PK to nonclustered and the index on
[Date] to clustered. If you often do range/grouping queries based on
[Date] then that should be useful, but it might also impact queries
using TroubleshootId, so you need to test any change with a number of
representative queries.

Other general advice would be to review the query plan in QA (Ctrl+K),
run UPDATE STATISTICS on the table, and also try tracing a typical
workload and running it through the Index Tuning Wizard to see what it
can recommend.

If you need more specific comments, you should post the query plan
(using SET SHOWPLAN_TEXT), and it might also be useful to know how many
rows are returned by the query.

Simon|||Yea ive tried a test on both and a query using the indexes take about
20 seconds less to run.

I tried the CTRL + L but its not making much sense to me.|||garydevstore (GaryDataStore@.gmail.com) writes:
> Which contains 5.6 Million rows and has non clustered indexes on Date,
> ReasonID, maj_Reason, Connection. Compared to other tables on the same
> server this one is extremely slow. A simple query such as :

Maybe some terminology is in order here. A road can be fast, but that
does not help you, if you car has a steering wheel out of order causing
you to zig-zag over the road. A car can be fast, but that does not help
if the road is in poor condition, so you cannot driver faster than 30 km/h
anyway.

In this case, the table is the road, and the query plan is the car. A
table itself does not move, but it can be badly fragmented in which case
it can be slow to drive through.

More likely, the query plan is not the best for the query. This is your
query:

> SELECT
> SD.reason_desc,
> SD.Duration,
> SD.maj_reason_desc,
> SD.[Connection],
> SD.aolEnteredBy
> FROM dbo.[Sherlock Data] SD
> Where SD.[Date] > Dateadd(Month,-2,Getdate())

There is a non-clustered index on Date. Assuming that rows are added
to this table regularly, there are presumably quite a few rows that
fits this condition. There are two ways for the optimizer to evaluate
this query: using the index, or scanning the table. The index is good
if only few rows are hit, but if many rows are hit the table scan is
faster. This is because, with the index you will need to read the same
page more than once.

The optimizer makes it choice of plan from the statistics SQL Server
has sampled about the table. The statistics may be out of date (even
if by default SQL Server auto-updates statistics). Try an UPDATE
STATISTICS WITH FULLSCAN, to see if this makes any difference.

But the road can also be in poor condition, that is the table can be
badly fragmented. This can be analysed with DBCC SHOWCONTIG and
remedied with DBCC DBREINDEX.

As suggested in other posts, you should look at the query plan, and see
if it says Clustered Index Scan or Index Seek + Bookmark Lookup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Simon Hayes wrote:
> One possiblility is to change the PK to nonclustered and the index on
> [Date] to clustered. If you often do range/grouping queries based on
> [Date] then that should be useful, but it might also impact queries
> using TroubleshootId, so you need to test any change with a number of
> representative queries.

A covering index might be an option, too, especially if there are several
queries with multiple criteria.

One question to the OP: why do you have Date and Time both as timestamp
columns? Other remarkable things: all char columns seem to be unicode
(nvarchar) and have length (255). You might save space by changing to
varchar (if possible) and / or reducing the length. Also, this doesn't
really look like a normalized schema. I would at least expect having ids
for EnteredBy and probably some others.

> Other general advice would be to review the query plan in QA (Ctrl+K),
> run UPDATE STATISTICS on the table, and also try tracing a typical
> workload and running it through the Index Tuning Wizard to see what it
> can recommend.
> If you need more specific comments, you should post the query plan
> (using SET SHOWPLAN_TEXT), and it might also be useful to know how
> many rows are returned by the query.

Plus complete index DDL.

robert

Extracting Time from the SQL DateTime field.

Any help on extracting the time part from the datetime field in SQL
database.

even though i had entered only the time part in the database when i
extract the field it gives me only the date part. im using Vb.net
datagrid as a front end.

any assistance appreciated!! :?:

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict254266.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877989"v0lcan0" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_877989_087c9582517dd6a1f7813c2a746cac72@.dbf orumz.com...
> Any help on extracting the time part from the datetime field in SQL
> database.
> even though i had entered only the time part in the database when i
> extract the field it gives me only the date part. i'm using Vb.net
> datagrid as a front end.
> any assistance appreciated!! :?:
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/General-Dis...pict254266.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=877989

See CONVERT() in Books Online:

select convert(char(8), getdate(), 108)

For more general information about working with datetime data, see here:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon

Extracting the date from a datetime value

Hi,
Is there a single command to extract the whole date part (day, month and
year) from a datetime value?
I have come across the Datepart function, which allows to extract, the day
or month or year depending on the parameters passed to it. Converting all of
the above to string, concatenating all the strings and converting to datetim
e
datatype seems to be a long procedure. Is there any other way to do this?
Thanks.
kdkd
SELECT CONVERT(CHAR(10),GETDATE(),121)
Note : take a look at 'style' the third parameter of the function
"kd" <kd@.discussions.microsoft.com> wrote in message
news:F330C878-4B5A-406C-B17B-7757CA603668@.microsoft.com...
> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to
datetime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Hi
Just go through this. This might be helpful
http://msdn.microsoft.com/library/d...br />
2f3o.asp
thanks and regards
Chandra
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||Here you go.
SELECT CONVERT(NVARCHAR, GETDATE(), 103)
Style 103 is the british/french output stype which gives you the following
output:
03/05/2005
Now to do this in the mm/dd/yy output you would do it as followed:
SELECT CONVERT(NVARCHAR, GETDATE(), 101)
Which gives you the output of:
05/03/2005
Just open books online and in search box type "convert date style" which
will give you the first returned results of "cast and convert" in transact
sql ,this shows you serveral was to convert the date into specific output
formats. Just pick one that is easiest for you to work with. The first one
I
listed above gives you exactly what you were asking for.
Hope this helps.
"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||This will give the date part of @.dt as a datetime value, without
requiring any conversions to character formats.
dateadd(day,datediff(day,0,@.dt),0)
Steve Kass
Drew University
kd wrote:

>Hi,
>Is there a single command to extract the whole date part (day, month and
>year) from a datetime value?
>I have come across the Datepart function, which allows to extract, the day
>or month or year depending on the parameters passed to it. Converting all o
f
>the above to string, concatenating all the strings and converting to dateti
me
>datatype seems to be a long procedure. Is there any other way to do this?
>Thanks.
>kd
>|||The convert() solutiions presented all convert the datetime value to a
string, and truncate the characters representing the time portion...
Another option to add to your bag of tricks relies on the fact that
DateTimes are stored internally as two integers, which present as a decimal,
the integer portion of which is the date, and the fractional portion of whic
h
is the time. So if you just strip off the fractional portion, you have a
date by itself.
Set NoCount On
Declare @.D DateTime Set @.D = '20050503 14:23:12'
Select @.D OrigDate
Set @.D = Floor(Cast(@.D As Float))
Select @.D ChangedDate"kd" wrote:

> Hi,
> Is there a single command to extract the whole date part (day, month and
> year) from a datetime value?
> I have come across the Datepart function, which allows to extract, the day
> or month or year depending on the parameters passed to it. Converting all
of
> the above to string, concatenating all the strings and converting to datet
ime
> datatype seems to be a long procedure. Is there any other way to do this?
> Thanks.
> kd|||While your solution works, it is not based on the internal
representation of datetime (and that's probably a good thing).
Converting datetime to float and back is a lot of work, and
your example converts 0x0000964900ED15C0 to
0x40E2C9332EA61D95, then applies floor to yield
0x40E2C92000000000, then converts back to the internal
datetime value 0x0000964900000000.
The basis for your solution is the interpretation SQL Server
provides to the user who is asking for a numerical value for
a datetime - SQL Server presents and understands numbers
as dates in terms of number of days from January 1, 1900.
That's part of the abstraction seen by users, and has nothing
to do with the implemenation. Your solution only needs
the cast from datetime to float and vice versa to work as
documented, which it should regardless of the internals.
A (less wise) solution that does depend on the 8-byte
(calling it two-integer is a stretch - bytes are bytes)
implementation of datetime values could use
Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
In any case, I think it's best to avoid implicit and explicit
conversions altogether, except for the one that converts
something typed in the query into a datetime value. I think
it was Gert-Jan who did some testing and found that this
is faster, too.
dateadd(d,datediff(d,0,@.D),0)
SK
CBretana wrote:
>The convert() solutiions presented all convert the datetime value to a
>string, and truncate the characters representing the time portion...
>Another option to add to your bag of tricks relies on the fact that
>DateTimes are stored internally as two integers, which present as a decimal
,
>the integer portion of which is the date, and the fractional portion of whi
ch
>is the time. So if you just strip off the fractional portion, you have a
>date by itself.
>Set NoCount On
>Declare @.D DateTime Set @.D = '20050503 14:23:12'
>Select @.D OrigDate
>Set @.D = Floor(Cast(@.D As Float))
>Select @.D ChangedDate"kd" wrote:
>
>|||Actually it sets the time to midnight that day. And depending on the
appliation you are using to view it it may shoul 12:00 PM in additon to the
date.
KD the real question is what do you intend to do with it? Is it for use in a
query or for presentation, if for presentation and you have mixed locals
where the standard date format i different then it is many times better to d
o
on the client side fo distributed apps. If web based then you need to take
into their local the proper format or opt for a specific format that you can
enforce.
"Steve Kass" wrote:

> This will give the date part of @.dt as a datetime value, without
> requiring any conversions to character formats.
> dateadd(day,datediff(day,0,@.dt),0)
> Steve Kass
> Drew University
> kd wrote:
>
>|||Steve,
I never thought to directly read the binary values and see what they
were... Thisis interesting... The binary value of '20050503 14:23:12' is
0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, but
no matter what I do, I can't figure out what SQL is doing with the low 4
bytes...
0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
24 hour day (86,400,000 millesconds) and it should be 51,791,971
(0.599444111110 of a 24 hr day).
The only thing I'm not clear on is why Casting to a Float should be
"expensive"... Is it because the system has to convert the binary data into
a
binary IEEE Mantissa/Exponent structure? I would have thought that would be
fairly performant, because it's a binary represeentation, which I "imagine"
all computers would somehow be internally optimized for, outside the scope o
f
whatever software was initiating this task. Not so? Why Not? (if you know
!)
Anyway,
"Steve Kass" wrote:

> While your solution works, it is not based on the internal
> representation of datetime (and that's probably a good thing).
> Converting datetime to float and back is a lot of work, and
> your example converts 0x0000964900ED15C0 to
> 0x40E2C9332EA61D95, then applies floor to yield
> 0x40E2C92000000000, then converts back to the internal
> datetime value 0x0000964900000000.
> The basis for your solution is the interpretation SQL Server
> provides to the user who is asking for a numerical value for
> a datetime - SQL Server presents and understands numbers
> as dates in terms of number of days from January 1, 1900.
> That's part of the abstraction seen by users, and has nothing
> to do with the implemenation. Your solution only needs
> the cast from datetime to float and vice versa to work as
> documented, which it should regardless of the internals.
> A (less wise) solution that does depend on the 8-byte
> (calling it two-integer is a stretch - bytes are bytes)
> implementation of datetime values could use
> Substring(Cast(@.D As Binary(8)),1,4)+0x00000000
> In any case, I think it's best to avoid implicit and explicit
> conversions altogether, except for the one that converts
> something typed in the query into a datetime value. I think
> it was Gert-Jan who did some testing and found that this
> is faster, too.
> dateadd(d,datediff(d,0,@.D),0)
> SK
> CBretana wrote:
>
>|||I think this is the thread that Steve is referring to:
http://groups.google.nl/groups?hl=n...>
dy.nl&rnum=4
(url may wrap)
HTH,
Gert-Jan
CBretana wrote:
> Steve,
> I never thought to directly read the binary values and see what they
> were... Thisis interesting... The binary value of '20050503 14:23:12' is
> 0x00009649 00ED15C0 -- I separated high 4 bytes from Low 4 bytes
> If you take the high 4 bytes, 0x9649, that is indeed hex for 38,473,
> ( 9*16*16*16 + 6*16*16 + 4*16 + 9), which is integer for May 3rd 2005, bu
t
> no matter what I do, I can't figure out what SQL is doing with the low 4
> bytes...
> 0x00ED15C0 hex is decimal 15,537,600. which is only .1798333333 of a full
> 24 hour day (86,400,000 millesconds) and it should be 51,791,971
> (0.599444111110 of a 24 hr day).
> The only thing I'm not clear on is why Casting to a Float should be
> "expensive"... Is it because the system has to convert the binary data int
o a
> binary IEEE Mantissa/Exponent structure? I would have thought that would
be
> fairly performant, because it's a binary represeentation, which I "imagine
"
> all computers would somehow be internally optimized for, outside the scope
of
> whatever software was initiating this task. Not so? Why Not? (if you kn
ow!)
> Anyway,
> "Steve Kass" wrote:
>

Extracting from a DATETIME data type

Hi,
I can't figure out how to extract just the date or just the time from a DATETIME data type? Any suggestions?SELECT CONVERT(varchar(10),GetDate(),101)
SELECT CONVERT(varchar(10),GetDate(),108)|||Thanks.