Solved

How to set formatdate on SQL 7.0

Posted on 2002-07-18
25
617 Views
Last Modified: 2011-08-18
Dear Adviser !

I use D5 + SQL 7.0

i set ShortDateFormat := 'dd/mm/yyyy'

when i insert a new record into ordertable, i use TEDIT (not TDBEDIT)

OrderTable.Appen
OrderTable.FieldByName('OrderDate').AsString := TEdit1.Text  {Why not error}

It is Ok.

But when i update with SQL statement :

ADOcommand.CommanTex := 'update order set orderdate = ' + QuotedStr(TEdit1.Text)

It occurs a error with "Wrong Date". Because SQL Server does not know the format 'dd/mm/yyyy'
If i use FormatDatTime('mm/dd/yyyy', the value of date) , it is ok.

How i set FormatDate on SQL Server ?
Explain for me, why not error at the second command above.

Thank for all consider
0
Comment
Question by:NamCit
  • 9
  • 7
  • 5
  • +2
25 Comments
 
LVL 1

Expert Comment

by:JDN
ID: 7164123
Hi,

Before your update-command you must force the date format on the SQL Server:

set dateformat ymd

JDN
0
 
LVL 1

Expert Comment

by:JDN
ID: 7164126
Sorry, in your case the format should be:

set dateformat dmy
0
 

Author Comment

by:NamCit
ID: 7164246

i should set dateformat before update every times ?

Could i update one time at begin of program ?

How i know the current set date in SQL Server ?  
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Expert Comment

by:JDN
ID: 7164311
I set the dateformat before every update where a date is involved. This way you are absolutely sure that the date is correctly interpreted.

The dateformat is no problem if you're using the datetime data type. But if you're passing the date as a string in an SQL command, you better always use the "set dateformat".
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7164322
For SQL Server, you should specify dates in the following format -

'{ ts 'yyyy-mm-dd hh:nn:ss'}'

for example -
  { ts '2002-07-11 09:57:00'}



In your example use -

var
  lDate: TDateTime;

begin
.
.
.
lDate := StrToDate(TEdit1.Text);
ADOcommand.CommandText := 'UPDATE order SET orderdate = { ts '''+ FormatDateTime('yyyy-mm-dd hh:nn:ss',lDate)+'''}';
.
.
end;
0
 
LVL 1

Expert Comment

by:JDN
ID: 7164334
This is more complicated than using the "set dateformat" i think.
0
 

Author Comment

by:NamCit
ID: 7164342

ShortDateFormat := 'dd/mm/yyyy'

OrderTable.Appen
OrderTable.FieldByName('OrderDate').AsString := TEdit1.Text

Why not error . Althought i did not set dateformat in SQL Server

i browse data on SQL, it is correct format like i done
 
0
 
LVL 1

Expert Comment

by:JDN
ID: 7164377
Delphi will convert the string correctly to your SQL Server date format.

I developed a lot Delphi/SQL-Server applications, and the date format is a nasty issue. Most of the time the date format in the SQL Server is different from the date format of the client.
You can change the date format of your SQL Server by changing the language. Use the sp_defaultlanguage stored procedure to do this (described in the T-SQL help file).

But, as I said, the best thing you can do is to force the date format in your SQL command with a "set dateformat".

JDN
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7164395
Using the 'ts' format is the SQL Server standard, you don't need to worry about setting date formats this way.

Its unfortunate that SQL uses [crazy ;)] US date formats for its standard strings...
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7165419
instead of

ADOcommand.CommanTex := 'update order set orderdate = ' + QuotedStr(TEdit1.Text)

use

ADOcommand.CommandText := 'update order set orderdate = ' + QuotedStr(formatDateTime('mm/dd/yyyy',strtodate(TEdit1.Text)));

just as workaround
(i've no sql-server, but maybe there is a dateformatfunction, you could use instead)

even not tetsted, because no sql-server

meikl ;-)
0
 

Author Comment

by:NamCit
ID: 7168468

Thanks for all
0
 

Expert Comment

by:kiranramesh
ID: 7168803
Hi NAMCIT,

To update date into MSSql server table the statement will be
Update table xyz set Convert(Char(8),col,112) = 'dd/mm/yyyy' where condition.

When you are selecting the statement will be

select Convert(Char(8),col,112) from table.

Regards
Kiran.
0
 

Author Comment

by:NamCit
ID: 7168812

Thanks Mr. kiranramesh
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7168846
PS - Kiran please can u post answers as comments in future - this allows the asker to evaluate the various answers :)
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7168853
Use style 103 for dd/mm/yyyy -

SELECT Convert(Char(10),OrderDate,103) FROM OrderTable
0
 
LVL 1

Expert Comment

by:JDN
ID: 7168865
Using a convert function in a SELECT query will prevent a RequestLive. This way you can only use this in read-only result sets.
0
 

Author Comment

by:NamCit
ID: 7168890

i will use

ADOcommand.CommandText := 'update order set orderdate = ' + QuotedStr(formatDateTime('mm/dd/yyyy',strtodate(TEdit1.Text)));

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7168981
if this works for u,
then reject kiranramesh proposed answer and
grade me instead ;-)
0
 

Author Comment

by:NamCit
ID: 7170797


i will use

ADOcommand.CommandText := 'set dateformat mdy update order set orderdate = ' + QuotedStr(formatDateTime('mm/dd/yyyy',strtodate(TEdit1.Text)));


How i sure that formatdate on SQL SerVer is mdy ?
So i add 'set dateformat mdy ' into SQL Stetemment. It works well.
0
 
LVL 1

Accepted Solution

by:
JDN earned 20 total points
ID: 7171123
NamCit,

First check the SQL Server language in the Enterprise Manager.
Select your server in the "SQL Server Group", right-click and select "Properties". In the General tab, the language is shown.

Then open the syslanguages table in the master database. You can do this also using the Enterprise Manager:
Select the master database, click "Tables", right-click "syslanguages" and select "Return all rows" from the pop-up menu.
In this table you'll see all languages with their dateformat setting, including the currently installed language you found the the General tab.

You can change the installed language using the sp_defaultlanguage stored procedure.

JDN
0
 

Author Comment

by:NamCit
ID: 7171287

Dear All !

Please show me the different between ADODataSet and ADOQuery

I only use ADOQuery for all SQL Statemment
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 7173478
? hmm,

i'm very disappointed about your
bahaviour of grading and questions

you grade here an additional question
>How i sure that formatdate on SQL SerVer is mdy ?
which is not part of the original q,
now you ask again about differences about
adoquery and adodataset

and this all for rarely 20 pts.

i will never give you an advice from now

regards

meikl :-(
0
 

Author Comment

by:NamCit
ID: 7173509

Dear kretzschmar !

I actualy do not know whom i should offer the point. Because every one above were willing to help me.

I think the point is not important. So that why i offer the lowest available point.

Please help me again in future.

I hope to receive a good new from you. IF i have done wrong way, show me , i will follow you

Sincerely

NamCit
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7173934
'I think the point is not important. So that why i offer the lowest available point.'

'Please help me again in future.'

Aren't these statements a bit on the selfish side? I'm with Meikl on this one :(

0
 

Author Comment

by:NamCit
ID: 7176034

YES, I am wrong. So what should i do next for my wrong thingking.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Delphi XE10, DigitalPersona Fingerprint reader and MySQL 6 333
Delphi TcxGrid group footer summary 3 317
delphi exception 7 68
Performance of SQL statement 37 127
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 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