[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Date Time format for Latvian Settings

SM

3/1/2007 7:21:00 AM

I am trying to fire a query having the WHERE clause on Date Time field. But
with Latvian Regional settings the date value comes as 'yyyy.mm.dd.'. The
last dot (.) in the string creates problem. Does anyone knows how to tackle
this?
6 Answers

Adi Cohn

3/1/2007 7:55:00 AM

0

On Mar 1, 9:21 am, SM <S...@discussions.microsoft.com> wrote:
> I am trying to fire a query having the WHERE clause on Date Time field. But
> with Latvian Regional settings the date value comes as 'yyyy.mm.dd.'. The
> last dot (.) in the string creates problem. Does anyone knows how to tackle
> this?

Why can't you make sure that the GUI that you are using will remove
the last dot?

Adi

Erland Sommarskog

3/1/2007 8:25:00 AM

0

SM (SM@discussions.microsoft.com) writes:
> I am trying to fire a query having the WHERE clause on Date Time field.
> But with Latvian Regional settings the date value comes as
> 'yyyy.mm.dd.'. The last dot (.) in the string creates problem. Does
> anyone knows how to tackle this?

I don't know you exact context, but there should rarely be any reason to
have date strings in queries at all. Use a parameterised query. Then the
conversion will happen client-side with knowledge of your regional settings,
and SQL Server will see a binary value.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...

SM

3/1/2007 8:36:00 AM

0

Thanks Adi,
But the problem is that after checking the date format for few more regional
settings I have found that this issue would arise with some more of them.
I.e. with regional settings as Bulgarian, I'd have to write code to remove
those other characters at the end of date value as well. Thats why I wanted
to have a generic solution. May be at the DB level.

"Adi" wrote:

> On Mar 1, 9:21 am, SM <S...@discussions.microsoft.com> wrote:
> > I am trying to fire a query having the WHERE clause on Date Time field. But
> > with Latvian Regional settings the date value comes as 'yyyy.mm.dd.'. The
> > last dot (.) in the string creates problem. Does anyone knows how to tackle
> > this?
>
> Why can't you make sure that the GUI that you are using will remove
> the last dot?
>
> Adi
>
>

SM

3/1/2007 8:43:00 AM

0

Thanks Erland,
First of all sorry for posting incomplete or confusing query. Actually I am
passing parameter as string to the Middle layer in my application. Because
this layer is generic hence I cannot explicitly type cast the values. Hence I
take the value passed through UI as it is & then fire it at the DB level. Now
when I fire the mentioned query with date value as '2007.03.01. 11:20:20'
then SQL Server throws an error. The error is of type casting, stating
converion of varchar value to datetime resulted in an over flow. And it is
understandeable that the last dot (.) in the date value is confusing SQL
Server. But why SQL Server does not understand this date format? And even if
it does not, is there an alternate or a provision for achieving this?

"Erland Sommarskog" wrote:

> SM (SM@discussions.microsoft.com) writes:
> > I am trying to fire a query having the WHERE clause on Date Time field.
> > But with Latvian Regional settings the date value comes as
> > 'yyyy.mm.dd.'. The last dot (.) in the string creates problem. Does
> > anyone knows how to tackle this?
>
> I don't know you exact context, but there should rarely be any reason to
> have date strings in queries at all. Use a parameterised query. Then the
> conversion will happen client-side with knowledge of your regional settings,
> and SQL Server will see a binary value.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/...
>

Uri Dimant

3/1/2007 8:57:00 AM

0

SM
SQL Server "loves" YYYYMMDD format


CREATE TABLE #t (dt DATETIME)
GO
DECLARE @dt DATETIME
SET @dt=CONVERT(VARCHAR(30),REPLACE('2007.03.01. 11:20:20','.','') ,112)

INSERT INTO #t SELECT @dt


SELECT * FROM #t

DELETE FROM #t





"SM" <SM@discussions.microsoft.com> wrote in message
news:8A0BE27A-B0B8-477C-B090-3ADD41D27354@microsoft.com...
> Thanks Adi,
> But the problem is that after checking the date format for few more
> regional
> settings I have found that this issue would arise with some more of them.
> I.e. with regional settings as Bulgarian, I'd have to write code to remove
> those other characters at the end of date value as well. Thats why I
> wanted
> to have a generic solution. May be at the DB level.
>
> "Adi" wrote:
>
>> On Mar 1, 9:21 am, SM <S...@discussions.microsoft.com> wrote:
>> > I am trying to fire a query having the WHERE clause on Date Time field.
>> > But
>> > with Latvian Regional settings the date value comes as 'yyyy.mm.dd.'.
>> > The
>> > last dot (.) in the string creates problem. Does anyone knows how to
>> > tackle
>> > this?
>>
>> Why can't you make sure that the GUI that you are using will remove
>> the last dot?
>>
>> Adi
>>
>>


Erland Sommarskog

3/1/2007 11:50:00 AM

0

SM (SM@discussions.microsoft.com) writes:
> First of all sorry for posting incomplete or confusing query. Actually I
> am passing parameter as string to the Middle layer in my application.
> Because this layer is generic hence I cannot explicitly type cast the
> values. Hence I take the value passed through UI as it is & then fire it
> at the DB level. Now when I fire the mentioned query with date value as
> '2007.03.01. 11:20:20' then SQL Server throws an error. The error is of
> type casting, stating converion of varchar value to datetime resulted in
> an over flow. And it is understandeable that the last dot (.) in the
> date value is confusing SQL Server. But why SQL Server does not
> understand this date format? And even if it does not, is there an
> alternate or a provision for achieving this?

As I said, best practice is to pass dates as binary values, and you should
seriously reconsider your design. A generic middle layer that passes SQL
statements with embedded parameters values causes several problems, and
this is one of the smallest.

A more serious issue is performance: if you use parameterised queries, the
query plans can be reused. With embedded parameters values, you get a new
cache entry for little change in the query string. This can have a serious
impact on the performance on the system.

An even more seroius issue is security. Have you heard about SQL injection?
This is a technique whereby intruder can enter text that results in
commands being executed that you did not intend to. For instance instead
of a date, the entry reads:

2007 ' DROP TABLE important --

If this fits in syntactically, you have just lost a table if permissions
permit.

In essence, you have a broken design, and you need to repair that. I guess
you can address the particular problem with the date format by adding code
to convert the date to a format that is good for SQL Server, that is
YYYY-MM-DDThh:mm.ss. SQL Server does not look at regional settings, but
has its own setting for date formats. For instance try:

SET DATEFORMAT dmy
SELECT convert(datetime, '1999-12-23')

This will fail.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/...