OT: Dates and DateFormat

[Carlos]

Hello,

I am trying works with diferent dateFormats in my app and I have many
problems:

If I use Spanish dateformat "dd/mm/yyyy" my queries works well when I use
dates:

Example SQL (MsAccess database)
"SELECT * ...vDate = #" + Date().FormatString("yyyy/mm/dd") + "#"

If I use German dateformat "dd.mm.yy" and I use
Date().FormatString("yyyy/mm/dd")
the result date is xxxx.xx.xx. Mask is not applied and query generates an
error.

Any suggerence ? common ways for avoid date problems in diferents formats

Thank you

[Gerhard Zampich]

Hello Carlos,

Post by Carlos
Example SQL (MsAccess database)
"SELECT * ...vDate = #" + Date().FormatString("yyyy/mm/dd") + "#"
If I use German dateformat "dd.mm.yy" and I use
Date().FormatString("yyyy/mm/dd")
the result date is xxxx.xx.xx. Mask is not applied and query generates
an error.

When I use dates in SQL, I use format "yyyymmdd" without any separator.
Mayby that will work in MSAccess too.

Regards
Gerhard

[Bernhard Geyer]

Post by Carlos
Any suggerence ? common ways for avoid date problems in diferents formats

Use Prepared Statements.

[Ray Marron]

Post by Carlos
Hello,
I am trying works with diferent dateFormats in my app and I have many
If I use Spanish dateformat "dd/mm/yyyy" my queries works well when I use
Example SQL (MsAccess database)
"SELECT * ...vDate = #" + Date().FormatString("yyyy/mm/dd") + "#"
If I use German dateformat "dd.mm.yy" and I use
Date().FormatString("yyyy/mm/dd")
the result date is xxxx.xx.xx. Mask is not applied and query generates an
error.
Any suggerence ? common ways for avoid date problems in diferents formats

Most SQL variants prefer ISO 8601 format, "yyyy-mm-dd". The problem you are
experiencing with the German format is that apparently Date().FormatString()
uses the date separator in the current date format wherever the generic
slash separator is found. Generally, the best way to avoid stuff like this
is to use query parameters (TParams) rather than try to hand-form your SQL.

[Deli Soetiawan]

you can hardcode the regional setting to your application, so whatever
regional setting on your client (and you) use, the application will always
behave in your setting

use this :

//can be put anywhere since it global procedure, and didn't need any
TForm, TDataModule, etc declaration
Procedure SetLocale;
begin
{ set localization ke ISO, International Standart }
DecimalSeparator := '.'; // you can adjust this
ThousandSeparator := ','; // you can adjust this too
LongDateFormat := 'yyyy-mm-dd hh:nn:ss'; // works well on all SQL
database (MSSQL, MySQL, Access, etc)
// there are some other too, example
// ShortDateFormat := 'dd/mm/yyyy';
// and there should be more...
end;

Post by Carlos
Hello,
I am trying works with diferent dateFormats in my app and I have many
If I use Spanish dateformat "dd/mm/yyyy" my queries works well when I
Example SQL (MsAccess database)
"SELECT * ...vDate = #" + Date().FormatString("yyyy/mm/dd") + "#"
If I use German dateformat "dd.mm.yy" and I use
Date().FormatString("yyyy/mm/dd")
the result date is xxxx.xx.xx. Mask is not applied and query generates
an error.
Any suggerence ? common ways for avoid date problems in diferents formats
Thank you

[Anders Isaksson]

Post by Deli Soetiawan
you can hardcode the regional setting to your application, so whatever
regional setting on your client (and you) use, the application will always
behave in your setting
//can be put anywhere since it global procedure, and didn't need any
TForm, TDataModule, etc declaration Procedure SetLocale;
begin
{ set localization ke ISO, International Standart }
DecimalSeparator := '.'; // you can adjust this
ThousandSeparator := ','; // you can adjust this too
LongDateFormat := 'yyyy-mm-dd hh:nn:ss'; // works well on all SQL
database (MSSQL, MySQL, Access, etc) // there are some other too, example
// ShortDateFormat := 'dd/mm/yyyy';
// and there should be more...
end;

Don't forget to set Application.UpdateFormatSettings := False; also, or your
program will break if the Windows locale settings are changed while it is
running!

[B]

Post by Carlos
Any suggerence ? common ways for avoid date problems in diferents formats

If you must form SQL manually, use TimeValue and DateValue functions
and format your date as yyyy-mm-dd.

http://office.microsoft.com/en-us/access/HA012288141033.aspx