CODEWITHSUNDEEP
mysqlsqldatabasedelphidate
Babah254
Babah254

Reputation: 13

Delphi SQL Date reading issue

I am currently creating a Delphi Application with SQL connections integrated.
SQL stores its date values in the format YYYY-MM-DD, however, when I read these values within my Delphi application, they are read in the format MM-DD-YYYY.

I have currently written the code to read the values of the dates from my database, however I am having trouble processing values which do not have 2 month integers or 2 day integers. For example, the date 17th of February 2013 would be read as '2/17/2013'

My current code separates the date into day, month and year. It works well for full dates (MM/DD/YYYY) as it reads the corresponding values within the text using the 'Copy' command within Delphi. When it reads a date which is in the format M/D/YYYY it does not copy the correct values from the string.

Is there any way to edit the string before I run my separation code, so that it forces the string into the format of MM/DD/YYYY. My idea is to write code to check the format of the string and then add any zeros which are missing: i.e change 2/17/2013 into 02/17/2013

I know I'm probably doing something to make things harder for myself, but this is my code at this point in time, so any help to fix my problem by adding to my current code would be much appreciated.

Thanks, A.

Upvotes: 1

Views: 4837

Answers (2)

jachguate
jachguate

Reputation: 17203

As a general rule, don't treat TDateTime values as strings, but as dates and times.

Don't get the value of a Date/Time field with the AsString method, use the AsDateTime method and assign it to a TDateTime variable.

If you want to know the date parts, use the provided functions to do so. For example the ones available in the DateUtils unit. The SysUtils unit also contains some Date/Time related functions.

uses
  DateUtils, SysUtils;

var
  MyDate: TDateTime;
  MyDay, MyMonth, MyYear: Word;
begin
  MyDate := MyQuery.Fields[3].AsDateTime;  //not AsString
  MyDay := DayOf(MyDate);
  MyMonth := MonthOf(MyDate);
  MyYear := YearOf(MyDate);
  ShowMessage(Format('Day: %d, Month: %d, Year: %d', [MyDay, MyMonth, MyYear]);

  //or also
  MyDate := EndOfTheMonth(MyDate);
  DecodeDate(MyDate, MyYear, MyMonth, MyDay);
  ShowMessage(Format('Day: %d, Month: %d, Year: %d', [MyDay, MyMonth, MyYear]);

The same applies to storing values to the database, rather than use a fixed date format, use parameters, like this:

uses
  DateUtils, SysUtils;

var
  MyDate: TDateTime;
  MyDay, MyMonth, MyYear: Word;
begin
  MyDate := EncodeDate(2013, 2, 17);
  MyQuery.SQL.Text := 'insert into myTable (MyDate) values (:MyDate)';
  MyQuery.Params.ParamByName('MyDate').AsDateTime := MyDate;
  MyQuery.ExecSQL();

It works with all the database access layers available that I know of.

Upvotes: 4

Dan Bracuk
Dan Bracuk

Reputation: 20804

I'm not familiar with Delphi, but if it's anything like .net, ColdFusion, php, or any other application technology, the principles are the same.

First, since you have a datetime datatype in your db, the only time format matters is when you display it. Dates are numbers, not strings.

Next, query parameters are your friend for a variety of reasons. If you can create a datetime variable in Delphi, and send it to MySql as a parameter, life will be very good.

Upvotes: 1

Related Questions