Re: I need some help here

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Joseph Byrns wrote:
You need to pass the date as a date rather than a varchar, so the procedure would look more like:

create proc test(
@date datetime)

as
begin

select name from table
where
DateEnt = @date

And I would also suggest you change the date to a long date string in the .NET part, so if you have a date object dt send dt.ToLongDateString (rather then the ShortDateString you are currently sending).


If you use parameters to call that sp, then you can just use
a DateTime value. No need to convert to string.


"Chris" <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:39925A92-79DB-48D8-BFB0-4DCCB9633E5E@xxxxxxxxxxxxxxxx

Hi,
I am storing dates in sql server in this format

2005-04-13 22:22:16.353

My asp.net app is passing the date as string like this 4/13/2005
to the foll sp


the *debugger* is *displaying* the DateTime value as mm/dd/yyyy,
it doesn't store it internally as string.




create proc test(
@date varchar(11))

as
begin

select name from table
where
LEFT( CONVERT(varchar, DateEnt, 120), 10)= @date

but that doesn't work only if I pass it as 2005-04-13 22:22:16.353. I tested
the proc in sql server

exec test '4/13/2005' but this doesn't work.

exec test '2005-04-14' this works.

Please advice.

Thanks
Saif 





--
Hans Kesting
.

Relevant Pages

  • Re: Failed to convert parameter value from a String to a DateTime
    ... perhaps the error occurs because SQL Server defaults to nvarchar when a parameter is NULL. ... I would try to declare the parameter as DateTime before to assign it the DBNull.Value. ... value may be a valid date such as '01/01/2007' or a null string. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Index performance revisited
    ... but could not find the proper ... I was trying to differentiate between a constant (like a string), ... In SQL Server, as well as the SQL standard, you do indeed ... specify datetime data as a string. ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem inserting dates to SQL Server
    ... The conversion of a char data type to a datetime data type resulted in ... an out-of-range datetime value. ... Are you passing the parameter in as string or as DateTime. ... But you are saying the error is still in around conversion, which indicates to me that you are passing a string into SQL server and hoping it can parse that into the correct type. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Passing DateDiff rather than Actual Dates as Parameters
    ... > Why pass it as a string and then convert it to a date? ... Why not just pass it to a datetime parameter in the first ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: I need some help here
    ... Error onverting datatype nvarchar to datetime. ... >> I am storing dates in sql server in this format ... >> create proc test( ...
    (microsoft.public.dotnet.framework.aspnet)