Date comparisons

[melo]

Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
procedures. Lately I found the following problem:

There's a stored procedure that has a DateTime input parameter (say
@statusDate). This parameter is passed from ADO.NET code as DateTime.
Inside stored procedure, there's a comparison of dates that looks like
"...WHERE createDate = @statusDate". Here, "createDate" is a column of
datetime type. The application was deployed on number of different
servers and worked just fine. Except one specific server where this
comparison fails, although the dates seem equal. I suspect that this
might have something to do with Locale/Regional Settings, although I
don't really see how they can impact in this case. Maybe someone can
give me a hint. Thanks.
--
Direct access to this group with [url]http://web2news.com[/url]
[url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

[Vishal Parkar]

I think you guessed it right. This must be problem with the server's
language settings. By default SQL server can understand format yyyymmdd.
hence while passing value make sure @statusdate variable's value matches
that format.

--
-Vishal
"melo" <melo.news.invalid@web2news.net> wrote in message
news:49099N829@web2news.com...

> Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
> procedures. Lately I found the following problem:
>
> There's a stored procedure that has a DateTime input parameter (say
> @statusDate). This parameter is passed from ADO.NET code as DateTime.
> Inside stored procedure, there's a comparison of dates that looks like
> "...WHERE createDate = @statusDate". Here, "createDate" is a column of
> datetime type. The application was deployed on number of different
> servers and worked just fine. Except one specific server where this
> comparison fails, although the dates seem equal. I suspect that this
> might have something to do with Locale/Regional Settings, although I
> don't really see how they can impact in this case. Maybe someone can
> give me a hint. Thanks.
> --
> Direct access to this group with [url]http://web2news.com[/url]
> [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

[Tibor Karaszi]

I suggest that you run a profiler trace and compare a working with the non-working SQL Server.
My guess is that the client application passes the date in a format which depends on regional
setting instead of a "safe" format.

--
Tibor Karaszi, SQL Server MVP
Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


"melo" <melo.news.invalid@web2news.net> wrote in message news:49099N829@web2news.com...

> Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
> procedures. Lately I found the following problem:
>
> There's a stored procedure that has a DateTime input parameter (say
> @statusDate). This parameter is passed from ADO.NET code as DateTime.
> Inside stored procedure, there's a comparison of dates that looks like
> "...WHERE createDate = @statusDate". Here, "createDate" is a column of
> datetime type. The application was deployed on number of different
> servers and worked just fine. Except one specific server where this
> comparison fails, although the dates seem equal. I suspect that this
> might have something to do with Locale/Regional Settings, although I
> don't really see how they can impact in this case. Maybe someone can
> give me a hint. Thanks.
> --
> Direct access to this group with [url]http://web2news.com[/url]
> [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]

[Tim]

The date conversion "failure" could be in the .Net code since within SQL
Server a date is a date is a double.

If the date settings in the control panel are (EG) d/m/y on the troublesome
machine, but m/d/y on all others this could happen when converting Text in
the UI to Date prior to putting it into the parameter value.

Using Profiler will help as then you will be able to see the parameter
values passed into SQL Server.

- Tim


"melo" <melo.news.invalid@web2news.net> wrote in message
news:49099N829@web2news.com...

> Hi. I'm using ADO.NET to retrieve data from SQL Server via stored
> procedures. Lately I found the following problem:
>
> There's a stored procedure that has a DateTime input parameter (say
> @statusDate). This parameter is passed from ADO.NET code as DateTime.
> Inside stored procedure, there's a comparison of dates that looks like
> "...WHERE createDate = @statusDate". Here, "createDate" is a column of
> datetime type. The application was deployed on number of different
> servers and worked just fine. Except one specific server where this
> comparison fails, although the dates seem equal. I suspect that this
> might have something to do with Locale/Regional Settings, although I
> don't really see how they can impact in this case. Maybe someone can
> give me a hint. Thanks.
> --
> Direct access to this group with [url]http://web2news.com[/url]
> [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]