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.
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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> 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.
>
|||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.
|||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/...mework ?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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegro ups.com...
> 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.
>
|||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.
|||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/...mework ?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" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegr oups.com...
> 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.
>
|||> 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/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegro ups.com...
> 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.
>
|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.
|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,
|||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" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegr oups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Showing posts with label pick. Show all posts
Showing posts with label pick. Show all posts
Thursday, February 16, 2012
Being DateTime Formats aware when accessing database
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.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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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... />
work?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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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... />
work?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" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> 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.
>|||> 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/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> 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.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, 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" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
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.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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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... />
work?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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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... />
work?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" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> 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.
>|||> 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/in...e.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lonifasiko" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> 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.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||Yep, I should add to the article that parametizing queries has bunch of adva
ntages, 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" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Being DateTime Formats aware when accessing database
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.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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> 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.
>|||> 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" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> 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.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||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" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
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.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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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" <mloichate@.gmail.com> wrote in message
news:1131553973.186824.98760@.o13g2000cwo.googlegroups.com...
> 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.
>|||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.|||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" <mloichate@.gmail.com> wrote in message
news:1131564804.366046.310110@.g43g2000cwa.googlegroups.com...
> 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.
>|||> 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" <mloichate@.gmail.com> wrote in message
news:1131563864.355665.61160@.g14g2000cwa.googlegroups.com...
> 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.
>|||Thanks Ilya for the "using parameters" advice. Many problems solved
this way.
Regards.|||Thanks Tibor for that great article but I think parameters have solved
all my headaches ;-)
Kind regards,|||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" <mloichate@.gmail.com> wrote in message
news:1131612217.129555.137660@.g47g2000cwa.googlegroups.com...
> Thanks Tibor for that great article but I think parameters have solved
> all my headaches ;-)
> Kind regards,
>
Sunday, February 12, 2012
Begin Date and End Date
Hi all
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please
SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004
I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please
SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004
I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO
Subscribe to:
Posts (Atom)