DateTime troubles

Try this format with your string:
yyyy-mm-dd

depending what date format you're server is set too

FieldByName('Date').value:=03/25/2005;
or
FieldByName('Date').value:=2005-03-25;

Best to find out what your server is set to.
You could have one of many more date formats listed above, so why not go to the database and find a table with a date column to check the format

var
  DT:    TDateTime;
  SD:    string;
begin
  SD := '20050305'; // or SD := '2005/03/05'; or ....... use what does not raise an exception on the next line
  DT := StrToDate(SD);
  FieldByName('Date').Value := TDateTime(DT);
end;

hi .. i think u have to use formatdatetime()
like this :

FieldByName.('Date').Value := FormatDateTime('yyyy/mm/dd',Date*)

*Date: will get the date of system so u can give an value to the field.

Hi,

you have to set MSSQL database to accept
your format, this can be done by sending
as a batch script:

SET DATEFORMAT ymd

(other formats: mdy,dmy,ydm,myd and dym)
(by default: mdy)

then set Delphi global variables like this:

ShortDateFormat:='yyyymmdd';
DateSeparator:='/';

regards,
Kate

doesn't anyone use parameters ?

qy.SQL.Text:='UPDATE Table SET Date = :Date WHERE ID = :ID';
qy.ParamByName('Date').AsDateTime:=SomeDate; // or EncodeDate(2005, 3, 5) or whatever :)
qy.ParamByName('ID').AsInteger:=SomeID;

works regardles of the system date format because the provider handles that

I guess not as much Lee  :o)

I know I don't

I believe trying to convert the date should be a last resort.  Remember I am stuck with the format yyyymmdd and I don't want to start adding slashes or dashes to make up what the strtodate is expecting.  

I prefer not to get into changing the date itself.    In fact (perhaps I gave too much information) the real problem is why won't the string date get forced into the date field in SQL.    I can do the same thing using an update statement?

As you can see however, I am treating the SQL table as a dataset because there's a lot of fields and I feel the code looks cleaner and easier to create than to have to create a messy update command.

Here's a code snippet (at the time):

               with quShipment do
                    begin
                      active := False;
                      sql.Text := 'select * from SHIPMENT_TRACKING_DETAILS where TRACKING_NUMBER = ''' + dxmemdata1.FieldByName('Tracking Number').AsString + '''';
                      active := True;


                      if RecordCount > 0 then  //tracking number already exists, what is this!  Fail the record and record in log
                        begin
                          MemLog.FieldByName('TrackingAlreadyExists').AsString :=  'True';
                          memlog.Post;
                        end
                      else // tracking number is a virgin, store this tracking number to that document
                        begin
                          append;
                          FieldbyName('DOC_NO').AsString          :=    quPo.FieldByName('Ref_no').AsString;
                          DateSeparator := '';
                          shortdateformat := 'yyyymmdd';
 ----------->>>    FieldByName('DELIVERY_DATE').asdatetime   :=    strtodate('20050305');
                          Post;

------------much deleted-----------------

Dateseparator := ''; will not compile btw

looknow12,

<<FieldByName('DELIVERY_DATE').asdatetime   :=    strtodate('20050305');>>
In order to use the strtodate() function, the string needs to be an acceptable date format.  Rreferencing http://www.delphibasics.co.uk/RTL.asp?Name=StrToDate&ExpandCode1=Yes
"The date string must adhere to the format of the ShortDateFormat value, and use the DateSeparator character to separate the day, month and year values. "

================================
<<FieldByName('Date').asstring := '20050305'>>
From this comment in your original question, I stated you needed to use the format yyyy-mm-dd.  This should be an acceptable date to SQL Server.  Did you try this?

================================
<<I believe trying to convert the date should be a last resort.  Remember I am stuck with the format yyyymmdd ...>>
* You currently have the yyyymmdd formatted string.  You are NOT 'stuck' with this.  This is merely a starting point.
* From a review of the experts' comments in this thread, I would say we all disagree with you.  This is the solution.  Please pick one and we will help you implement it.  If you do not know how to reformat this string, please ask and we will be glad to help.

I'd rewrite that to:

with quShipment do
begin
  if Active then
    Close;
  sql.Text := 'select * from SHIPMENT_TRACKING_DETAILS where TRACKING_NUMBER = :TN';
  ParamByName('TN').AsString:=dxmemdata1.FieldByName('Tracking Number').AsString;  // if the Tracking number is really an integer then use AsInteger
  Open;

  // use (Eof and Bof) to determine that the resultset is empty - RecordCount executes the query again !!!
  if not (Eof and Bof) then  //tracking number already exists, what is this!  Fail the record and record in log
  begin
    MemLog.FieldByName('TrackingAlreadyExists').AsString :=  'True';
    memlog.Post;
  end
  else // tracking number is a virgin, store this tracking number to that document
  begin
    Append;
    FieldbyName('DOC_NO').AsString          :=    quPo.FieldByName('Ref_no').AsString;
    FieldByName('DELIVERY_DATE').AsDateTime   :=    EncodeDate(2005, 3, 5);
    Post;

where do you get that date from anyway ?

and update statements are not messy unless you make them messy
in your case why do you retrieve the whole row if you just wanna know if there's a record that fits that criteria ?
instead you should only do

with quShipment do
begin
  if Active then
    Close;
  sql.Text := 'select Count(*) as RecCount from SHIPMENT_TRACKING_DETAILS where TRACKING_NUMBER = :TN';
  ParamByName('TN').AsString:=dxmemdata1.FieldByName('Tracking Number').AsString;  // if the Tracking number is really an integer then use AsInteger
  Open;
  if FieldByName('RecCount').AsInteger > 0 then ...

try

onformCreate:

Application.UpdateFormatSettings := False;
ShortDateFormat := 'yyyyMMdd';


I recall reading somewhere that windows overrides the shordate assignation by default.
The first line of the code above stops this from happening.

May work, may not - but its not an overly complex solution to try :)

Accepted Answer from TheRealLoki
Date: 03/28/2005 06:52PM PST
Grade: A
 Accepted Answer  


just ignore all the horrible date time formats the system and/or the sql server, and simply do :-
s := '20050305';  // yyyymmdd
FieldByName('DELIVERY_DATE').asdatetime := EncodeDate( StrToInt(copy(s, 1, 4)), StrToInt(copy(s, 5, 2)), StrToInt(copy(s, 7, 2)) );


Thanks, that was the easiest solution to implement.