Where does Gupta read dateformat from?

forum.advanced.programming (1998-2005)
ozmenbetul
Turkey
Posts: 291
Joined: 28 Aug 2019, 08:57
Location: Turkey

Where does Gupta read dateformat from?

Post by ozmenbetul » 29 Mar 2005, 14:12

 Posted by:  Betul Ozmen 

Hi,
I'm trying to use ODBC and OleDb connections in my project, determining
which one to use by observing a parameter in an "ini" file. Sql statements
have been reviewed to eliminate odbc-only implementations such as
sub-queries and so on. Meanwhile, it is MS Sql Server 2000 and TD 2.0 where
I'm running my tests.

Here is an occasion where I read some information from the database :

Set sSql = "Select StudentID, StudentName
into :xID, :xName
From Students
where Registration_Date BETWEEN :xDate1 AND :xDate2"
which prepares ands executes succesfully (both odbc and oledb supports this
type of parameter using)

1 )Set sSql = "SELECT X.DepartmentID, X.StudentNumber, D.DepartmentName
INTO :xID, :xCount, :xName
FROM
( Select DepartmentID, count(StudentID) As
StudentNumber
From Students
where Registration_Date BETWEEN :xDate1 AND
:xDate2
Group By DepartmentID
) X, Departments D
WHERE X.DepartmentID = D.DepartmentID
ORDER BY X.DeparmentID"

is a succesfull statement that executes on odbc, though it should be
implemented as below to be executed succesfully on oledb :

2) Set sSql = "SELECT X.DepartmentID, X.StudentNumber, D.DepartmentName
INTO :xID, :xCount, :xName
FROM
( Select DepartmentID, count(StudentID) As StudentNumber
From Students
where Registration_Date BETWEEN \'" || xDate1 || "\'
AND \'" || xDate2 || "\'
Group By DepartmentID
) X, Departments D
WHERE X.DepartmentID = D.DepartmentID
ORDER BY X.DeparmentID"

In other words, all parameters used in temporary tables like the "X" table
above, should be converted to string first, and shouldn't be used directly.

It is all right for number and string parameters, but datetime parameters
rise a serious problem which is in fact directly related to datetime format.
Sometimes I see that if I format xDate1 as :

Set dateformat = "dd/MM/yyyy"
Call SalFmtFormatDateTime( xDate1, dateformat )
and run the sql statement(2) it works, and on some clients if it datetime
format is "MM/dd/yyyy", it works.

It is possible to put a parameter into an "ini" file and choose the date
format according to this parameter, but the question is this : How will I
decide which format to use? What influences Sql Server about which format to
handle? A simple sql statement can easily handle direct parameter using (
such as ':xDate1' ). So where does it read the dateformat from ? From Sql
Server registration, or anywhere else?

I hope it is clear what I mean.
And sorry for such a long long explanation.

Any help will be so valuable.
Regards
Betul

Ifo

Re: Where does Gupta read dateformat from?

Post by Ifo » 29 Mar 2005, 19:09

 Posted by:  Ifo 

Hi,
if I understand, the problem is with DATE or DATETIME
columns in your database. If you try to use date in
WHERE clause as string constant, you should use appropriate
format, that is different for each SQL server. We refused
this technique because of problems, that you mentioned.
We now use :
SELECT ... FROM ... WHERE Datecolumn BETWEEN :dMin and :dMax
("dMin" and "dMax" are Date/Time variables).
Variables are set-up before each SqlExecute.

We tried this way on many SQL servers (Oracle, MS SQL,
PostgreSQL) with no problem.

--
Ifo, tangram software, Slovakia

ozmenbetul
Turkey
Posts: 291
Joined: 28 Aug 2019, 08:57
Location: Turkey

Re: Where does Gupta read dateformat from?

Post by ozmenbetul » 31 Mar 2005, 12:39

 Posted by:  Betul Ozmen 

Thank you for the answer.
The problem is that, while connecting via oledb, in virtual tables such as
"X" in my above example, you are not allowed to use ":dfMin" syntax,
instead, you are supposed to use string implementation.
:(
Regards
Betul

Return to “advanced.programming”

Who is online

Users browsing this forum: [Ccbot] and 0 guests