Delphi SQL Date reading issue

Question

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.

wanna show us the place where you read your db field value? it can help a lot, but as an idea, you can do Query.FieldMyDateTimeField.AsDateTime => TDateTime value which you can then format as you please i.e. DateToStr(MyDateTimeValue, MyTFormatSettings); — user497849
they are datetime values within mysql and string values within my dlephi application - using Fields[i].AsString to read the values from a DBGrid — Babah254
I've changed my code to read the values as TDateTime values, however, they are in the same format of MM/D/YYYY. How would I use the TFormatSettings to configure the layout of these values? thanks, A — Babah254
@user2057482 declare a variable as TFormatSettings and set the date and/or time format as you please, i.e. MyFormatSettings.Short|LongDateFormat, MyFormatSettings.Shot|LongTimeFormat, etc. don't forget about the MyFormatSettings.Date|TimeSeparator char — user497849

jachguate jachguate · Accepted Answer · 2013-02-17T17:20:17

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.

Delphi SQL Date reading issue

2 Answers