Issue 72146 - date conversion in macro SQL code
Summary: date conversion in macro SQL code
Status: CLOSED FIXED
Alias: None
Product: App Dev
Classification: Unclassified
Component: api (show other issues)
Version: 3.3.0 or older (OOo)
Hardware: All Windows 98
: P3 Trivial
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@api
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2006-11-30 16:54 UTC by sarotti
Modified: 2013-02-24 21:08 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description sarotti 2006-11-30 16:54:24 UTC 
Hi,

problem with RC1 of OO 2.1:



there is a problem with a date code in macro programming

example for the SQL-Code: 

Select * FROM `table_x` WHERE `date1` ={D '2006-11-29'} AND `date2` = {D '2006-
11-29'} AND ....

This SQL command is working in my MySQL database (as well in the native SQL 
Code and also as an OO query). If I use an "executeQuery" command in a macro 
code, the date code is converted. Now there are slashes "/" (... `date1` =#2006/
11/29# ....) in the command, as a result there occures a runtime error in basic 
(syntax error in query). 

Also changing the language settings doesn´t change this....

Thank you for your help

best regards
sarotti
Comment 1 jsc 2006-12-01 12:13:55 UTC 
jsc -> fs: can you please verify or comment this issue
Comment 2 Frank Schönheit 2006-12-04 11:59:55 UTC 
could you please attach a sample Basic script?
Comment 3 sarotti 2006-12-04 21:15:22 UTC 
Sorry, I checked everything (also I changed to 2.1 RC2) and now the error is 
not reproducible. I made some tests, and maybe it is a (or my) mistake in 
declaration the variables. I made the experience that SQL Macro Code with a 
date (DIM xxx as Date) is criticel to use when you create a query. 
But when you use a date-string it´s easier. I wrote a small function to convert 
a date in the standard SQL string (using the command CDateToIso) and now it´s 
working fine.


function function_Date_Convert(date_basis as string)

'// this function converts a date in a usable SQL date

Dateconv=CDateToIso(date_basis)
sYear=left(Dateconv, 4)
sDay=right(Dateconv, 2)
sMonth=mid(Dateconv,5,2)
function_Date_Convert=sYear + "-" + sMonth + "-" + sDay

end function

Using this the date format is independent on the language code and it´s easier 
to handle. Now it´s possible to use it like this: 

....
sDate="01.01.2006"
sDate=function_Date_Convert(sDate)

sSQL="SELECT * FROM `table1` WHERE `Datefield` = #" + sDate + "'"
resultSet=executeQuery(sSQL)
.....

So, think it´s solved now

Thank you for your cooperation

sarotti
Comment 4 christoph.lukasiak 2007-06-26 15:00:00 UTC 
-> close