Being DateTime Formats aware when accessing database

[Lonifasiko]

Maybe has been answered many times but here I go:

I pick up a date from MonthCalendar control in my device running
Spanish OS version. The format can be this: 23/11/2005 00:00:00.

The server side SQL Server 2005 April CTP is in English and I'm using
Merge Replication to fill a SQL Mobile 2005 database, also in English.

Logically, I'm having trouble with datetime columns.

Do I have to convert Spanish datetime picked up in the interface to
English datetime format everytime I must do an update or insert?

Is there any way to avoid this, to be database datetime format aware?

I was initially trying this, but failed of course:

UPDATE APPOINTMENT SET appointmentDate = '23/11/2005 0:0:00'
WHERE appointmentId = 1

Then, after manually building English DateTime format, this query
worked.

Changing to UPDATE APPOINTMENT SET appointmentDate = '11/23/2005
0:0:00'
WHERE appointmentId = 1

But that's not the way. I have to avoid manally building string that
will be inserted as DateTimes.

Any solution will be greatly appreciated.

[Tibor Karaszi]

I think that this will answer most of your questions:
http://www.karaszi.com/SQLServer/info_datetime.asp

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Lonifasiko" <mloi...@gmail.com> wrote in message
news:1131553973....@o13g2000cwo.googlegroups.com...

[Lonifasiko]

Thanks Tibor, but I still have got one question:

If I use language neutral unseparated format ('19980223 14:23:05') and
execute an insert or and update statement both against Spanish SQL
Server and English SQL Server, both statements will work without
problem?

Therefore, don't worrying about SQL Server language, if I convert my
DateTime value (programming in C# for example) to string datatype in
unseparated format and execute the statement, will work? That is, SQL
Server will be able to understand it and store it in the appropiate
format?

I'll try tomorrow but can't be so easy.

What about finding rows that match a concrete date where clause? I'm
using LIKE operator but it is rather language dependant: If SQL Server
in Spanish, "Ago 10,2005%" is the pattern. If in English, "Aug
10,2005%" is the pattern. Can this also be solved wihout using BETWEEN
and "<" and ">" operators, only with LIKE?

Thanks very much.

[Ilya Tumanov [MS]]

You should always use parameters and pass DateTime instead of string.

That would work for any locale and would also improve performance.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

"Lonifasiko" <mloi...@gmail.com> wrote in message
news:1131553973....@o13g2000cwo.googlegroups.com...

[Lonifasiko]

Do you mean always use parameters with update/insert statements against
SQL Mobile 2005 and SQL Server CE databases?

Any known problems when synchronizing with SQL Server (Spanish and
English versions mainly) via Merge Replication?

Could you please post an example of an update statement using
parameters?

Thanks very much. I'm willing to try tomorrow morning.

[Ilya Tumanov [MS]]

Yes, always. That makes conversion unnecessary and eliminates format
problems completely.

For example, DateTime is format-less, it's just a 64 bit integer.

The problem with formats only arises if you convert it to/from string. Which
also takes a lot of time.

Update/Insert sample is available in VS documentation, just look up
SqlCeCommand.Parameters property.

If your SQL Server DB uses collation which is not supported on the device,
you'll get NotSupportedException on attempt to replicate.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

"Lonifasiko" <mloi...@gmail.com> wrote in message

news:1131564804.3...@g43g2000cwa.googlegroups.com...

[Tibor Karaszi]

> If I use language neutral unseparated format ('19980223 14:23:05') and
> execute an insert or and update statement both against Spanish SQL
> Server and English SQL Server, both statements will work without
> problem?

Yes.

> What about finding rows that match a concrete date where clause?

Did you read the article? I have a long section about just that:
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Lonifasiko" <mloi...@gmail.com> wrote in message

news:1131563864....@g14g2000cwa.googlegroups.com...

[Lonifasiko]

Thanks Ilya for the "using parameters" advice. Many problems solved
this way.

Regards.

[Lonifasiko]

Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)

Kind regards,

[Tibor Karaszi]

Yep, I should add to the article that parametizing queries has bunch of advantages, among others
that ADO (or whatever API is used) will handle these things for you.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"Lonifasiko" <mloi...@gmail.com> wrote in message

news:1131612217.1...@g47g2000cwa.googlegroups.com...