I need some help here

[Chris]

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

"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:39**********************************@microsof t.com...

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

[Hans Kesting]

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" <Ch***@discussions.microsoft.com> wrote in message
news:39**********************************@microsof t.com...

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

[Chris]

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

"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:39**********************************@microsof t.com...

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]

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)
"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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

"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:39**********************************@microsof t.com...

> 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

[Chris]

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" wrote:

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)
"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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

"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:39**********************************@microsof t.com...
> 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]

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?

"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:A2**********************************@microsof t.com...

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" wrote:

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)
"Chris" <Ch***@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

> 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" 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).
>>
>> "Chris" <Ch***@discussions.microsoft.com> wrote in message
>> news:39**********************************@microsof t.com...
>> > 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
>>
>>
>>