default Datetime parameter

Hi,
I'm editing some reports at the moment, and they've been set up using start
and end date parameters both having datetime datatype.
They both have a default value as the user would like them to run
immediately, the problem is they are both set to Date.Now() so no
information is coming out as the start date is now! i would like the start
date to default to 5 yrs in the past, what can i type in. I'd like it to
use the Date.Now() or getdate() methods or something like that so it will
change automatically.
any suggestions would be much appreciated.
cheers GregSet the default value to =System.DateTime.Now.AddYears(-5).|||Set the default value to =System.DateTime.Now.AddYears(-5).|||To do the same thing via sql... create a dataset with
select dateadd(yy,-5,getdate())
and use this as the default...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Potter" wrote:
> Set the default value to =System.DateTime.Now.AddYears(-5).
>|||cheers guys,
went for potter's solution as it's running off a stored procedure and
couldn't be bothered to change it.
thanks
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Ok new problem, and in fact part fo the reason why i asked the firsdt
question.
The report has a hyperlink to drillthrough to the next report, the
parameters are passed through by the hyperlink, the problem is i keep
getting an error on the start date. They are both set as datetime
datatypes. The error is
The value provided for the report parameter 'StartDate' is not valid for its
type. (rsReportParameterTypeMismatch)
cheers
Greg
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:D8794D05-5CCD-4F96-92DE-09EB773C8F9C@.microsoft.com...
> To do the same thing via sql... create a dataset with
> select dateadd(yy,-5,getdate())
> and use this as the default...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Potter" wrote:
>> Set the default value to =System.DateTime.Now.AddYears(-5).
>>|||Greg,
How are you going about this hyperlinking? Are you using 'jump to
report' or are you using the 'jump to url'?
Is the value for the date in the hyperlink actually the parameter value
(ie - =Parameters!Date.Value) or is it a value from a dataset (ie
=Fields!Date.Value)?
Potter|||Jump to report and it's literally just passing the parameter from the first
report to the parameter in the second report.
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Also for the record, the links work fine in preview mode it's only a problem
when viewed through report manager
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> Greg,
> How are you going about this hyperlinking? Are you using 'jump to
> report' or are you using the 'jump to url'?
> Is the value for the date in the hyperlink actually the parameter value
> (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> =Fields!Date.Value)?
> Potter
>|||Try explicitly converting the value passed in the Jump To. ie =Convert.ToDateTime(Parameters!DateParam.Value)
Greg wrote:
> Also for the record, the links work fine in preview mode it's only a problem
> when viewed through report manager
> "Potter" <drewpotter@.gmail.com> wrote in message
> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
> > Greg,
> >
> > How are you going about this hyperlinking? Are you using 'jump to
> > report' or are you using the 'jump to url'?
> >
> > Is the value for the date in the hyperlink actually the parameter value
> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
> > =Fields!Date.Value)?
> >
> > Potter
> >|||cheers, i haven't tried that as i have an in house written report viewer
which seems to deal with it no problem. So i'm not worried about report
manager having problems with it anymore. Thanks for your help though
Greg
"Potter" <drewpotter@.gmail.com> wrote in message
news:1134784157.263872.184070@.g47g2000cwa.googlegroups.com...
> Try explicitly converting the value passed in the Jump To. ie => Convert.ToDateTime(Parameters!DateParam.Value)
> Greg wrote:
>> Also for the record, the links work fine in preview mode it's only a
>> problem
>> when viewed through report manager
>> "Potter" <drewpotter@.gmail.com> wrote in message
>> news:1134671641.653682.112660@.o13g2000cwo.googlegroups.com...
>> > Greg,
>> >
>> > How are you going about this hyperlinking? Are you using 'jump to
>> > report' or are you using the 'jump to url'?
>> >
>> > Is the value for the date in the hyperlink actually the parameter value
>> > (ie - =Parameters!Date.Value) or is it a value from a dataset (ie
>> > =Fields!Date.Value)?
>> >
>> > Potter
>> >
>sql

default datetime error

hi friends,

i have two datetime parameters in my report ... based on these parameters , am searching records ... the issue is i cant get the full datetime value

for example

i am searching records based on 03/16/2007 and 03/20/2007... i can't the records for the date 03/20/2007...

bcoz, the value of the date is '03/20/2007 00:00:00.000'

i want to get the value like this ' 03/20/2007 11:59:59 pm''

can any one help me

You can specify the time part for the parameter and retrieve the records. I mean, you can type 3/20/2007 11:59:59 PM in the parameter field.

Shyam

|||

but the issue is i just want to give my date only... like this 3/20/2007

... it has to take the time by default 11:59:59 PM ....

|||

You have to specify the expression for your date parameter in dataset. For example, if your parameter name is @.to_date in your stored procedure, the report parameter name would be to_date.

Go to Data tab and select the appropriate dataset from the dropdown and then click the Edit (...) button besides it. Go to Parameters tab and for the @.to_date parameter, give the expression for Value as:

=DateAdd(DateInterval.Second, -1, DateAdd(DateInterval.Day, 1, Parameters!to_date.Value))

This will add 1 day to your given date and then subtract 1 second from that date and this value will be passed on to the stored procdure or SQL query (whichever you have used)

Shyam

|||i am pleased to thank you shyam...thanx for your help .. i have implemented ...|||

I know this solution is definitely working. But just wanted to be perfect on this. :-) The "correct" way to do something like this is to offset 3 milliseconds instead of 1 second. HTH.

Default date parameter

I have two date parameters, a start and end date. I want the end date to
default to the start date if the user does not fill out the field for the end
date. If they do fill out the end date, then I want them to take this.
This should be really simple but everything I've tried has not worked. How
do you get SRS to do this? I have tried setting default values in Report
Parameters screen and I've also messed with the Allow Null Value checkbox.
Do I need to check this also? Please let me know how to configure the Report
Parameters.
Thank you.Ryan,
If I understand you correctly, you should be able to set the EndDate
parameter to 'Allow Null'.
In you SQL, check the parameter values passed in and conditionally
assign the EndDate the value of the StartDate if the EndDate is null.
Andy Potter

default date in a subscription

Hello! How can i put a default date in one of my parameters in a subsciption?
i.e : each day - i want 'today' date in the parameter...
Thanks> Hello! How can i put a default date in one of my parameters in a
subsciption?
> i.e : each day - i want 'today' date in the parameter...
Use the Today(), Now() or DateString() VB functions for the default value
expression, e.g.
=DateString()
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hello!
I did try the functions you wrote - but it seems that i can not do that in a
subscription - only in the report parameter.
I want to be able to do that when i define a subscription...
Thanks Any Way and if you have other idea - i will be happy if you wrote back.
Thanks!
"Penker" wrote:
> Hello! How can i put a default date in one of my parameters in a subsciption?
> i.e : each day - i want 'today' date in the parameter...
> Thanks|||When you create a subscription, you have possibility to use the default
values of the report parameters (at the bottom of the "New Subscription"
page). So, just put the VB function in the expression for the default value
of the report parameter, and then use this default value in your
subscription.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Penker" <Penker@.discussions.microsoft.com> wrote in message
news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> Hello!
> I did try the functions you wrote - but it seems that i can not do that in
a
> subscription - only in the report parameter.
> I want to be able to do that when i define a subscription...
> Thanks Any Way and if you have other idea - i will be happy if you wrote
back.
> Thanks!
> "Penker" wrote:
> > Hello! How can i put a default date in one of my parameters in a
subsciption?
> > i.e : each day - i want 'today' date in the parameter...
> > Thanks|||I tried this several ways to accomplish this and have been unsuccessful. From
what i've read it seems you must set the default params in reoprt designer.
Still having trouble on how to accomplish that. But once I do here is the
function I had planned on using, if you figure out how to set the default
param values in report designer please post. Thanks...
=datetime.today.adddays(-1) --ive tested this in a textbox and it works...
"Dejan Sarka" wrote:
> When you create a subscription, you have possibility to use the default
> values of the report parameters (at the bottom of the "New Subscription"
> page). So, just put the VB function in the expression for the default value
> of the report parameter, and then use this default value in your
> subscription.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Penker" <Penker@.discussions.microsoft.com> wrote in message
> news:0A004250-7E9A-4511-8A38-E8B94F2D7B34@.microsoft.com...
> > Hello!
> > I did try the functions you wrote - but it seems that i can not do that in
> a
> > subscription - only in the report parameter.
> > I want to be able to do that when i define a subscription...
> > Thanks Any Way and if you have other idea - i will be happy if you wrote
> back.
> > Thanks!
> >
> > "Penker" wrote:
> >
> > > Hello! How can i put a default date in one of my parameters in a
> subsciption?
> > > i.e : each day - i want 'today' date in the parameter...
> > > Thanks
>
>

Default DATE and uniqueidentifier parameters?

I have several stored procedures and to facilitate getting the output of the stored procedures we have been adding default values for all of the input parameters. This works fine with the exception of DATE and uniqueidentifier parameters. I have defined stored procedures like:

ALTER PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = NEWID

AS

and

ALTER PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = GETDATE,

@.DateTo datetime = GETDATE

but when I execute the following

SET FMTONLY ON

exec proc_shippedPackages

SET FMTONLY OFF

I get

Msg 241, Level 16, State 1, Procedure proc_shippedPackages, Line 0

Conversion failed when converting datetime from character string.

Any suggestions? The same error occurs with setting a uniqueidentifier. I want to create a default parameter that will more of less ensure that the output is empty.

Thank you.

Kevin

You could try the example below.

Beware, though, that if you do need to explicitly set the values of the @.DateFrom and @.DateTo parameters to NULL when calling the stored procedure [as opposed to simply not providing values for these optional parameters] then the parameters will be assigned a value of GETDATE() during execution, which may or may not be what you want. If this does turn out to be a problem then you could use an arbitrary [but unlikely to be used] date as the default value and then check for that value rather than NULL when assigning the value of @.Now.

Chris

Code Snippet

CREATE PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom DATETIME = NULL,

@.DateTo DATETIME = NULL

AS

--Ensure that both variables are set to

--equal values if defaults are required.

DECLARE @.Now DATETIME

SET @.Now = GETDATE()

IF @.DateFrom IS NULL

BEGIN

SET @.DateFrom = @.Now

END

IF @.DateTo IS NULL

BEGIN

SET @.DateTo = @.Now

END

SELECT @.DateFrom, @.DateTo

GO

|||

It is not bad idea to have NON-NULL values, suppose if you need to store / pass the NULL value from your code the below code wont break.

Code Snippet

Alter PROCEDURE [dbo].[proc_GetOrderReasonByOrderGroupId]

@.OrderGroupId uniqueidentifier = 0x0

AS

Select@.OrderGroupId = Case When @.OrderGroupId = 0x0 Then NewId() Else @.OrderGroupId End

go

Alter PROCEDURE [dbo].[proc_shippedPackages]

@.DateFrom datetime = '1900-01-01 00:00:00.000',

@.DateTo datetime = '1900-01-01 00:00:00.000'

as

SET @.DateFrom = Case When @.DateFrom = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateFrom End

SET @.DateTo = Case When @.DateTo = '1900-01-01 00:00:00.000' Then GetDate() Else @.DateTo End

|||GETDATE and NEWID are functions and require () after them, unlike VB. Try:

@.OrderGroupId uniqueidentifier = NEWID()

@.DateFrom datetime = GETDATE(),

@.DateTo datetime = GETDATE()

|||TPhillips -> You are wrong; SP params only support the constant/NULL value as default value.|||Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

If you check the syntax with the Sql Management Studio it complains if you add the ().

|||

Kevin,

You cannot use the NEWID() and GETDATE() functions as default values in the parameter definition.

DEFAULT value assignments must be deterministic. Non-deterministic functions are not permitted in that context.

If your intent is to make the parameters optional, use '01/01/1900' (or NULL), then in the first lines of the sproc, check the values and if = '01/01/1900' (or NULL), then set the values = getdate().

Your original attempt failed because you are setting the default values to the string constants 'GETDATE' and 'NEWID.

NEWID() and GETDATE() both require parentheses as previously mentioned.

|||

Tom Phillips wrote:

Yes, you are correct. Your method, mentioned earlier, is the way to fix this problem.

However, the () are still needed to execute the functions.

When I include the () I get:

Msg 102, Level 15, State 1, Procedure proc_GetCaseNotesByOrderGroupID, Line 4

Incorrect syntax near '('.

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NEWID()

AS

|||As mentioned, you have to set the default to a "static", you cannot use a function on a default value.

What your code was doing without the () is equivalent to:

@.OrderGroupID uniqueidentifier = 'NEWID'

I assume you did not want the @.orderGroupID to be a string NEWID. I think this is a bug or at least hold over from Sybase which allows unquoted strings to be invisibly converted to a string.

The best way to do what you want is:

ALTER PROCEDURE [dbo].[proc_GetCaseNotesByOrderGroupID]

@.OrderGroupID uniqueidentifier = NULL -- or some other non-occurring number

AS

IF @.OrderGroupID IS NULL
SET @.OrderGroupID = NEWID()

sql

Default Date

Hi,

I have two parameters as StartDate and End Date. these dates are picked from the Datepicker.

What I want to do is to default the start date to the the 1st January of the current year and end date as 31st december of the current year.

I tried to use the default in 'Report Parameters'. But could not get through. Can anyone suggest me the solution.

regards

Josh

Under default values of the report parameters are, you can use expressions to create your default dates (using non-queried option)

EG, 1 Jan in current year, expression would be

=DateSerial(Year(Today),1,1)

and 31 Dec

=DateSerial(Year(Today),12,31)

|||

Hi Will,

Thanks a lot! This worked and was very helpful.

Regards

Josh

Declaring and Using report parameters

I need to declare a report parameter, for example BegPeriod. Once that
parameter has been declared I need to work the parameter. For example the
parameter needs to evaluated with the following statement: "if
right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the value
in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
that will need to be done for all months.
Will Reporting Services do this? If so, can you direct me to the correct
method.
Thank you,
normadReport parameters are easy to declare. Go to layout tab and report menu,
report parameters. Add a parameter.
Now, you don't really say where you want to use this expression. Is
EarnDed00 a field in a dataset? Are you wanting to use this expression to
pass a value to a stored procedure? Are you wanting the expression to be
shown in a field of the table object?
Read up on expressions. And note, when you create a query parameter RS
automatically creates a report parameter but you don't have to use it, you
can map a query parameter to an expression.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NormaD" <NormaD@.discussions.microsoft.com> wrote in message
news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
>I need to declare a report parameter, for example BegPeriod. Once that
> parameter has been declared I need to work the parameter. For example the
> parameter needs to evaluated with the following statement: "if
> right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
> value
> in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
> that will need to be done for all months.
> Will Reporting Services do this? If so, can you direct me to the correct
> method.
> Thank you,
> normad
>|||Bruce: Thank you. I want this expression to be shown in the report. Can
this be done?
Norma
"Bruce L-C [MVP]" wrote:
> Report parameters are easy to declare. Go to layout tab and report menu,
> report parameters. Add a parameter.
> Now, you don't really say where you want to use this expression. Is
> EarnDed00 a field in a dataset? Are you wanting to use this expression to
> pass a value to a stored procedure? Are you wanting the expression to be
> shown in a field of the table object?
> Read up on expressions. And note, when you create a query parameter RS
> automatically creates a report parameter but you don't have to use it, you
> can map a query parameter to an expression.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NormaD" <NormaD@.discussions.microsoft.com> wrote in message
> news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
> >I need to declare a report parameter, for example BegPeriod. Once that
> > parameter has been declared I need to work the parameter. For example the
> > parameter needs to evaluated with the following statement: "if
> > right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
> > value
> > in EarnDed00 else 0 Jan". This returns a month, year and deduction value.
> > that will need to be done for all months.
> >
> > Will Reporting Services do this? If so, can you direct me to the correct
> > method.
> >
> > Thank you,
> >
> > normad
> >
> >
>
>|||Easily. If you are using the table control add another column (right mouse
click on a column and add one to the left or right of an existing one). Then
do a right mouse click on the new field in the detail row and pick
expression. This brings up the expression builder. Read up in books online
how to write expressions. You can easily do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NormaD" <NormaD@.discussions.microsoft.com> wrote in message
news:FD3119FF-7E16-4B81-B7B9-DBC6AC230A5F@.microsoft.com...
> Bruce: Thank you. I want this expression to be shown in the report. Can
> this be done?
> Norma
> "Bruce L-C [MVP]" wrote:
>> Report parameters are easy to declare. Go to layout tab and report menu,
>> report parameters. Add a parameter.
>> Now, you don't really say where you want to use this expression. Is
>> EarnDed00 a field in a dataset? Are you wanting to use this expression to
>> pass a value to a stored procedure? Are you wanting the expression to be
>> shown in a field of the table object?
>> Read up on expressions. And note, when you create a query parameter RS
>> automatically creates a report parameter but you don't have to use it,
>> you
>> can map a query parameter to an expression.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "NormaD" <NormaD@.discussions.microsoft.com> wrote in message
>> news:98012AA4-B6EA-4270-8BFB-265E8E35152B@.microsoft.com...
>> >I need to declare a report parameter, for example BegPeriod. Once that
>> > parameter has been declared I need to work the parameter. For example
>> > the
>> > parameter needs to evaluated with the following statement: "if
>> > right(BegPeriod, 2) = '01' and left(BegPeriod, 4) = '2006' then use the
>> > value
>> > in EarnDed00 else 0 Jan". This returns a month, year and deduction
>> > value.
>> > that will need to be done for all months.
>> >
>> > Will Reporting Services do this? If so, can you direct me to the
>> > correct
>> > method.
>> >
>> > Thank you,
>> >
>> > normad
>> >
>> >
>>

Debugging Stored Proc

Hi,
When I go to debug any SP thru Object browser in query analyser ... it
starts well ... takes all the required parameters to begin ... but when I
click on execute button, it completes the SP execution immediately. It does
not allow to use functions like Step into, Step over etc ... Why does this
happen?
Because of this, my main purpose of debugging any SP, does not get satisfied
.
Pls Help."Krishnapra Paralikar" <Krishnapra
Paralikar@.discussions.microsoft.com> wrote in message
news:9DDB8999-67C1-44C8-ADD6-B2979892E612@.microsoft.com...

> When I go to debug any SP thru Object browser in query analyser
http://www.sql-server1.org/detail-7330883.html

Debugging Query Analyzer & dates

Two of the sprocs input parameters are datetime. When I try to debug the
sproc SQL Server errors with
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
I've tried everything (except of course what might actually work).
Help!!!
regards
Frank Ashley
Try any of the ODBC styles, as defined in:
http://msdn.microsoft.com/library/en...on_03_04l0.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
> Two of the sprocs input parameters are datetime. When I try to debug the
> sproc SQL Server errors with
>
> [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
> specification
>
> I've tried everything (except of course what might actually work).
>
> Help!!!
> regards
> Frank Ashley
>
|||That worked first time.
Thanks
Frank Ashley
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OZ0mPj4gEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Try any of the ODBC styles, as defined in:
> http://msdn.microsoft.com/library/en...on_03_04l0.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Frank Ashley" <fashley_NO_SPAM@.clusterseven.com> wrote in message
> news:uG9xu73gEHA.632@.TK2MSFTNGP12.phx.gbl...
>

Debugging Parameterized Queries

How would I debug such a query.

I have a sqlCommand to which I add several parameters for an insert statement.

if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?

I have also been looking for this, but there does not seem to be a public property of the Command object that exposes this. I don't think it is actually stored in the object anywhere. I think it is created on the fly, when sent to the database.

What you can do however, if you don't have too many parameters, is to just copy/paste the SQL command into your TOAD. If you prefix your OracleParameters with : (instead of @.) then TOAD will ask you for the value of each parameter as you run your query.

Another option (although a bit cumbersome) is to write a function that actually parses the CommandText property and inserts the current values of the parameters, with respect to their datatype... But it would take some work to get it right ;-)

Debug with datetime datatype

Hi,
When I debug a stored procedure, if one of the input parameters is
datetime datatype, it doesn't matter what I type in, i got an error said
"Invalid character value for cast specification" Any idea what value I need
to key in?
example
create procedure ztest
@.Input smalldatetime
as
If @.input = '5/1/2005'
print 'Good'
Else
print 'Bad'
in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
Thanks
EdI believe that one of below formats will work:
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:62386CA6-E369-4D53-9BB6-13A80A175E53@.microsoft.com...
> Hi,
> When I debug a stored procedure, if one of the input parameters is
> datetime datatype, it doesn't matter what I type in, i got an error said
> "Invalid character value for cast specification" Any idea what value I ne
ed
> to key in?
> example
> create procedure ztest
> @.Input smalldatetime
> as
> If @.input = '5/1/2005'
> print 'Good'
> Else
> print 'Bad'
> in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
> Thanks
> Ed
>|||Ed,
It's not you. The debugger data entry for datetime is buggy. ;)
Use the format
YYYY-MM-DD HH:MM:00.000, or for your date
2005-05-01 00:00:00.000
Put all the zeros in, even though you just have a smalldatetime (even if
you had datetime, you need to make them zero, because the debugger
doesn't handle more precision).
Here are a couple of old newsgroup threads on this bug:
http://groups-beta.google.com/group...ldatetime&hl=en
Steve Kass
Drew University
Ed wrote:

>Hi,
> When I debug a stored procedure, if one of the input parameters is
>datetime datatype, it doesn't matter what I type in, i got an error said
>"Invalid character value for cast specification" Any idea what value I nee
d
>to key in?
>example
>create procedure ztest
> @.Input smalldatetime
>as
>If @.input = '5/1/2005'
> print 'Good'
>Else
> print 'Bad'
>in debug mode, i key in '5/1/2005' for @.Input, but error occurs.
>Thanks
>Ed
>
>