NamCit
asked on
How to set formatdate on SQL 7.0
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('Or derDate'). 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
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('Or
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'
How i set FormatDate on SQL Server ?
Explain for me, why not error at the second command above.
Thank for all consider
Sorry, in your case the format should be:
set dateformat dmy
set dateformat dmy
ASKER
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 ?
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".
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".
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;
'{ 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
.
.
end;
This is more complicated than using the "set dateformat" i think.
ASKER
ShortDateFormat := 'dd/mm/yyyy'
OrderTable.Appen
OrderTable.FieldByName('Or
Why not error . Althought i did not set dateformat in SQL Server
i browse data on SQL, it is correct format like i done
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
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
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...
Its unfortunate that SQL uses [crazy ;)] US date formats for its standard strings...
instead of
ADOcommand.CommanTex := 'update order set orderdate = ' + QuotedStr(TEdit1.Text)
use
ADOcommand.CommandText := 'update order set orderdate = ' + QuotedStr(formatDateTime(' mm/dd/yyyy ',strtodat e(TEdit1.T ext)));
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 ;-)
ADOcommand.CommanTex := 'update order set orderdate = ' + QuotedStr(TEdit1.Text)
use
ADOcommand.CommandText := 'update order set orderdate = ' + QuotedStr(formatDateTime('
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 ;-)
ASKER
Thanks for all
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.
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.
ASKER
Thanks Mr. kiranramesh
PS - Kiran please can u post answers as comments in future - this allows the asker to evaluate the various answers :)
Use style 103 for dd/mm/yyyy -
SELECT Convert(Char(10),OrderDate ,103) FROM OrderTable
SELECT Convert(Char(10),OrderDate
Using a convert function in a SELECT query will prevent a RequestLive. This way you can only use this in read-only result sets.
ASKER
i will use
ADOcommand.CommandText := 'update order set orderdate = ' + QuotedStr(formatDateTime('
if this works for u,
then reject kiranramesh proposed answer and
grade me instead ;-)
then reject kiranramesh proposed answer and
grade me instead ;-)
ASKER
i will use
ADOcommand.CommandText := 'set dateformat mdy update order set orderdate = ' + QuotedStr(formatDateTime('
How i sure that formatdate on SQL SerVer is mdy ?
So i add 'set dateformat mdy ' into SQL Stetemment. It works well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear All !
Please show me the different between ADODataSet and ADOQuery
I only use ADOQuery for all SQL Statemment
? 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 :-(
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 :-(
ASKER
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
'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 :(
'Please help me again in future.'
Aren't these statements a bit on the selfish side? I'm with Meikl on this one :(
ASKER
YES, I am wrong. So what should i do next for my wrong thingking.
Before your update-command you must force the date format on the SQL Server:
set dateformat ymd
JDN