I need some help here

[Guest]

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



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

 

[Joseph Byrns]

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).

 

[Hans Kesting]

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.


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

 

[Guest]

Hi,
When I try

New SqlParameter("@date", (Convert.ToDateTime("4/13/2005")).ToLongDateString)

I get the error

Error onverting datatype nvarchar to datetime.

 

[Joseph Byrns]

That's because it is getting the American and European dates mixed up, if
you do:

Convert.ToDateTime("13/4/2005")).ToLongDateString

it will work fine. Or you can do as Hans suggested and enter it as a date
object in the parameter.
New SqlParameter("@date", aDateObject)

 

[Guest]

This is my setup

Private Sub GetReport(ByVal sDate As String)

.....New SqlParameter("@date",
(Convert.ToDateTime(sDtae)).ToLongDateString)


End Sub


And I am calling it like this

GetReport(txtDate.text)


txtDate receives the date in the format 4/13/2005

This doesn't work.

 

[Joseph Byrns]

Yes, it is just that the date is the wrong way round (mm/dd/yyyy instead of
dd/mm/yyyy), presumably, txtDate is a text box, can you not use a
DateTimePicker instead? Or drop down lists for the date, that way you can
ensure the date is in the correct order before you start?