DateTime validation throgh sql query

hai friends,
how can i made validation of date time through sql query?

Swati

Check out the SQL Function: IsDate()|||Validation is best done from front end technology rather than make a round trip to the DB just to validate a date field.

DateTime Parameter Validation

Hello Group,
I am having a report parameter which is of the type DateTime. I get a simple
textbox for that and if the user types in some invalide date an exception is
thrown on the UI. I want to handle this parameter as soon as user types it
in and clicks on the 'View Report' button. Can i some how pass this typed-in
value to the VB.Net code that i will write in the rdl and validate it there
itself? I can pass it to my stored proc by making the type of the parameter
as 'String' and do the validation there. But don't want to pass this value
to the stored proc.
Thanks in advance
AtulI have an example on www.msbicentral of a method to use a string as a data
parameter, but format it using functions... ( the name is something like
formatted date...)
I wonder if you couldn't do the same as in the example, except instead of
calling the format method in VB.net, call Code.whatever, and use a code
function which you have written...I've never done exactly that but it might
be worth a few minutes to try...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
> simple textbox for that and if the user types in some invalide date an
> exception is thrown on the UI. I want to handle this parameter as soon as
> user types it in and clicks on the 'View Report' button. Can i some how
> pass this typed-in value to the VB.Net code that i will write in the rdl
> and validate it there itself? I can pass it to my stored proc by making
> the type of the parameter as 'String' and do the validation there. But
> don't want to pass this value to the stored proc.
> Thanks in advance
> Atul
>|||Is there a reason you are using text for the datetime parameter? You can
have it be datetime. Menu Report, Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hello Group,
> I am having a report parameter which is of the type DateTime. I get a
simple
> textbox for that and if the user types in some invalide date an exception
is
> thrown on the UI. I want to handle this parameter as soon as user types it
> in and clicks on the 'View Report' button. Can i some how pass this
typed-in
> value to the VB.Net code that i will write in the rdl and validate it
there
> itself? I can pass it to my stored proc by making the type of the
parameter
> as 'String' and do the validation there. But don't want to pass this value
> to the stored proc.
> Thanks in advance
> Atul
>|||Hello Bruce,
If i keep the parameter as datetime, and if the user types in some invalid
date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
parameters' input and not any aspx page. If i want to avoid the exception i
will have to accept it as a string and pass it to the stored proc and chekc
the validity of the user input there.
Atul
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> Is there a reason you are using text for the datetime parameter? You can
> have it be datetime. Menu Report, Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Atul" <atul.anjankar@.approva.net> wrote in message
> news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Hello Group,
>> I am having a report parameter which is of the type DateTime. I get a
> simple
>> textbox for that and if the user types in some invalide date an exception
> is
>> thrown on the UI. I want to handle this parameter as soon as user types
>> it
>> in and clicks on the 'View Report' button. Can i some how pass this
> typed-in
>> value to the VB.Net code that i will write in the rdl and validate it
> there
>> itself? I can pass it to my stored proc by making the type of the
> parameter
>> as 'String' and do the validation there. But don't want to pass this
>> value
>> to the stored proc.
>> Thanks in advance
>> Atul
>>
>|||Your choice is to accept the UI from the Report Manager (which in most cases
what I do) or pass it to the SP for validation.
True, this "The value provided for the report parameter 'StartDate' is not
valid for its type. " is not the most user friendly message.
Still, I haven't really had a problem with the users. I'm afraid that we
don't have a whole lot of options with this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Atul" <atul.anjankar@.approva.net> wrote in message
news:uugSVIzHFHA.3612@.TK2MSFTNGP09.phx.gbl...
> Hello Bruce,
> If i keep the parameter as datetime, and if the user types in some invalid
> date, MSSSRS throws and exception on the UI. I using MSSSRS's UI for the
> parameters' input and not any aspx page. If i want to avoid the exception
i
> will have to accept it as a string and pass it to the stored proc and
chekc
> the validity of the user input there.
>
> Atul
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:e0$ga%23nGFHA.3076@.tk2msftngp13.phx.gbl...
> > Is there a reason you are using text for the datetime parameter? You can
> > have it be datetime. Menu Report, Parameters
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Atul" <atul.anjankar@.approva.net> wrote in message
> > news:evPcP$jGFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> Hello Group,
> >> I am having a report parameter which is of the type DateTime. I get a
> > simple
> >> textbox for that and if the user types in some invalide date an
exception
> > is
> >> thrown on the UI. I want to handle this parameter as soon as user types
> >> it
> >> in and clicks on the 'View Report' button. Can i some how pass this
> > typed-in
> >> value to the VB.Net code that i will write in the rdl and validate it
> > there
> >> itself? I can pass it to my stored proc by making the type of the
> > parameter
> >> as 'String' and do the validation there. But don't want to pass this
> >> value
> >> to the stored proc.
> >>
> >> Thanks in advance
> >> Atul
> >>
> >>
> >
> >
>sql

Dates, stored procedures and asp.net

Hi,
I am having problems with dates and timestamps...

I have a textbox that allows the user to enter a date - the format is ccyymmdd. I have validation on this to ensure it is in the correct format.

I pass this as a parameter to a sql server stored procedure. In the stored procedure that parameter is defined as datetime and the field in the database it is also datetime.

To cut a long story short...an exception is being thrown! I have convinced myself it is due to the date - everything else I have used before. I am not sure whether I need to do a conversion in SQL perhaps prior to insert but not sure how to do this in any case, or, whether I have to do something in the vb code before loading the parameter??

CREATE PROCEDURE [dateexample]
(@.SomeDate [datetime])

AS INSERT INTO [testdate]
([SomeDate])

VALUES
(@.SomeDate)
GO

Anyone had similar problems / suggestions ? If this is not posted in an appropriate forum I apologise like I say I'm not sure which side the solution lies...

Thanks to all who take the time to read thistry changing your SP to this one..

CREATE PROCEDURE dateexample (@.SomeDate datetime)
AS
begin
INSERT INTO testdate VALUES (@.SomeDate)
end
GO

|||Is the exception being thrown by SQL Server, or by your vb code?|||Just to make sure, since you mention timestamps in your original post. I hope your problem isn't that you're trying to use a timestamp field to store a date/time.
A timestamp is not a date or time field. It's a serial number. The name is somewhat misleading. It's one of the unfortunate legacy things from back in the Sybase days.

From SQL Books-on-line...

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server? may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server? 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.