Board index » delphi » SQL and Date format - Am I correct?

SQL and Date format - Am I correct?

Quote
Paul Bailey wrote:

> Hi all,

> I read somewhere that in order to use SQL and Dates, the date format has
> to be MM/DD/YY. Is this correct?
> Here in South Africa, our format is DD/MM/YY, but using this seems to
> chuck my SQL queries around, EVEN THO I have set the BDE date fromat to
> Mode 1.

> Any thoughts or clarification on the matter? Thanks.

> Thanks
> PAUL
> pa...@maties.sun.ac.za

This depends heavily on which database type you are using.  
Some examples:

If database is Access, use this:
  select * from mytable where (thedate = #07/22/1997#)

If database is Oracle, use this:
  select * from mytable where (thedate = '22-Jul-1997')

If database is Informix, use this:
  select * from mytable where (thedate = '1997-07-22')

Hope this helps
-  Rich

 

Re:SQL and Date format - Am I correct?


Hi all,

I read somewhere that in order to use SQL and Dates, the date format has
to be MM/DD/YY. Is this correct?
Here in South Africa, our format is DD/MM/YY, but using this seems to
chuck my SQL queries around, EVEN THO I have set the BDE date fromat to
Mode 1.

Any thoughts or clarification on the matter? Thanks.

Thanks
PAUL
pa...@maties.sun.ac.za

Re:SQL and Date format - Am I correct?


I'd be very happy to be corrected, but I think that the BDE date
configuration is a separate issue from SQL dates.

I have BDE set up for DD/MM/YYYY, but for SQL statements (set up
programmatically) I use:
  firstDate := FormatDateTime('dd-mmm-yyyy', StrToDate(Edit1.Text));
which seems to work fine.

In article <01bca211$b76dc760$56e06ccb@default>, "Fred Troncone"

Quote
(tronc...@ozemail.com.au) writes: >Paul Bailey

<pa...@maties.sun.ac.za> wrote in article
Quote
><33E7ACAF....@maties.sun.ac.za>...
>> I read somewhere that in order to use SQL and Dates, the date format has
>> to be MM/DD/YY. Is this correct?
>> Here in South Africa, our format is DD/MM/YY, but using this seems to
>> chuck my SQL queries around, EVEN THO I have set the BDE date fromat to
>> Mode 1.

>> Any thoughts or clarification on the matter? Thanks.

>> Thanks
>> PAUL
>> pa...@maties.sun.ac.za

>I have had the same problem here in Australia. From what I have read, the
>BDE will only interpret dates in the format mm/dd/yy. There is a way around
>it though:
>    Get your user to type the date in whatever format you want (eg
>dd/mm/yy), convert the string into a TDateTime type and then format the
>TDateTime type to mm/dd/yy.

>I use the following code to change the date string (in format dd/mm/yy) in
>Edit1 to a date string in Edit2 in format mm/dd/yyyy. I then use Edit2 as a
>parameter for my query. Of course you could just use a string variable
>instead of Edit2 if appropriate.

>---------------------------------------------------
>var
>  ADate: TDateTime;
>begin
>     try
>        ADate := StrToDate(Edit1.Text);
>        Edit2.Text := FormatDateTime('mm/dd/yyyy', ADate);
>     except
>        on EConvertError do
>                MessageDlg('Not a valid date.', mtInformation, [mbOk], 0);
>     end; {try}
>end;

Re:SQL and Date format - Am I correct?


Paul Bailey <pa...@maties.sun.ac.za> wrote in article
<33E7ACAF....@maties.sun.ac.za>...

Quote
> Hi all,

> I read somewhere that in order to use SQL and Dates, the date format has
> to be MM/DD/YY. Is this correct?
> Here in South Africa, our format is DD/MM/YY, but using this seems to
> chuck my SQL queries around, EVEN THO I have set the BDE date fromat to
> Mode 1.

You must set your date format correctly in the Reginal Settings / Date
dialog to conform with the local convention as well. That will ensure that
your dates are properly formatted throughout. I Have done this
successfully.

Re:SQL and Date format - Am I correct?


Hi!

You can also set it in your code rather than in the Regional Settings which
affects your the whole Windows OS.

Try the following in your *main* form somewhere, I put mine in my main
FormCreate.

        DateSeparator := '/';
        ShortDateFormat := 'dd/mm/yyyy';

Cheers!

Laurence Liew
Singapore

Norman <nmcsy...@iafrica.com> wrote in article
<01bca438$87dc0b00$LocalHost@norman-lt>...

Quote

> Paul Bailey <pa...@maties.sun.ac.za> wrote in article
> <33E7ACAF....@maties.sun.ac.za>...
> > Hi all,

> > I read somewhere that in order to use SQL and Dates, the date format
has
> > to be MM/DD/YY. Is this correct?
> > Here in South Africa, our format is DD/MM/YY, but using this seems to
> > chuck my SQL queries around, EVEN THO I have set the BDE date fromat to
> > Mode 1.
> You must set your date format correctly in the Reginal Settings / Date
> dialog to conform with the local convention as well. That will ensure
that
> your dates are properly formatted throughout. I Have done this
> successfully.

Other Threads