?
Solved

DateTime troubles

Posted on 2005-03-26
15
Medium Priority
?
2,216 Views
Last Modified: 2011-10-03
Hello,

I'm trying to post a date (yyyymmdd) into a SQL table that uses a date type field.  Every time I do so with Query Analyzer in the same format, it works fine, but in Delphi using...

FieldByName('Date').asstring := '20050305'

I receive 20050305 is not a valid date.

So then I figured I would try to use

FieldByName('Date').asdatetime := strtodate('20050305')

And that fails with the same error.  I also tried to set ShortDateFormat to = 'yyyymmdd' but with no luck.

Please help.
0
Comment
Question by:looknow12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +7
15 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 13637728
Try this format with your string:
yyyy-mm-dd
0
 

Expert Comment

by:ralph44e
ID: 13637765
depending what date format you're server is set too

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

Expert Comment

by:mikelittlewood
ID: 13637989
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:esoftbg
ID: 13638563
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;
0
 
LVL 1

Expert Comment

by:RYasin
ID: 13638687
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.

0
 
LVL 10

Expert Comment

by:_Katka_
ID: 13638721
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


0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 13638782
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
0
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13638936
I guess not as much Lee  :o)

I know I don't
0
 

Author Comment

by:looknow12
ID: 13639175
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-----------------


0
 

Author Comment

by:looknow12
ID: 13639176
Dateseparator := ''; will not compile btw
0
 
LVL 46

Expert Comment

by:aikimark
ID: 13639466
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.
0
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 13639570
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 ...


0
 
LVL 1

Expert Comment

by:delphinewbie
ID: 13642119
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 :)

0
 
LVL 17

Accepted Solution

by:
TheRealLoki earned 2000 total points
ID: 13649051
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)) );
0
 

Author Comment

by:looknow12
ID: 13712961
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.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month8 days, 8 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question