| T O P I C R E V I E W |
| additive |
Posted - 09/23/2010 : 04:44:52 AM
Origin Ver. 8.1 SR2
Operating System: Windows XP
Hi,
when getting data from a MS SQL database into Origin I encountered some problems with date columns. On my system the date format depends on Windows' regional settings. On the system of a customer, the date in the Origin column has the format "YYYY-MM-DD", independent of the regional settings. Can I enforce Origin to use a specific date format? Is there any option I can add to the connection string or PutRecordset method?
Any advice is highly appreciated!
/ Michael |
| 6 L A T E S T R E P L I E S (Newest First) |
| cpyang |
Posted - 09/25/2010 : 12:04:00 PM
quote:
Originally posted by cpyang
So you basically want an option to allow default date column format to be specified such that OC code to PutRecordset can force date column (if has Date in the result) to be forced to that specified format, so you don't need to worry about destination or if contents might contain date.
There is already a POSTREADS structure added in 8.1 to allow nCodePage to be specified, so we can add another member to allow Date format to be specified as well.
Hi Michael
On further consideration, I now see there is no reason to change POSTREADS to add such option, since your OC code can easily format the column post PutRecordset anyway.
What might work better is for Origin to provide a general support via Tools->Options->Numeric Format tab to add a new entry
Date Column Format : Windows Setting
so default will use Windows setting when setting a new column as date but to allow specifying a Custom Date format like "yyyy-MM-dd". Such settings are part of LT acccessible System. properties and your program can just set it once and then from now on all DB or ASCII import with date will be set to such format.
CP
|
| Sophy |
Posted - 09/25/2010 : 04:06:59 AM
Hi, Michael:
When importing, Origin only knows that it is a date/time field, but we don't know the format string like 'yyyy/MM/dd' or other format, what's more, the field value is sent from SQL Server as a string and Origin just get the value and put to worksheet cells. However, if you know the field name you are going to retrieve, can use SQL function 'CONVERT' to specify what you want the result value to be displayed, like "yyyy-mm-dd hh:mm:ss" or "mm/dd/yyyy hh:mm:ss.mmm", please refer to the following example, hope it will help.
#include <Origin.h>
//the following code assume that there exist a ODBC datasource named sqlsever_qa2 which connect to SQL Sever sample database "NorthWind"
//for more format, see http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx
void test_format_date()
{
string strConn = "Data Source=sqlserver_qa2;User ID=test;Password=test;";
string strQuery = "Select FirstName, LastName, CONVERT(char(25), Employees.BirthDate, 120), CONVERT(char(25), Employees.HireDate, 121) from Employees";
Object ors;
try
{
ors = CreateObject("ADODB.Recordset");
}
catch(int nErr1)
{
printf("Fail to create COM Object : ADODB.Recordset.\n");
return;
}
try
{
ors.Open(strQuery, strConn, 0, 1);
}
catch(int nErr2)
{
printf("Fail to execute SQL statement.\n");
return;
}
//dump to active worksheet.
Worksheet wks = Project.ActiveLayer();
if ( !wks )
{
printf("Fail to get active worksheet.\n");
return;
}
try
{
wks.PutRecordset(ors);
}
catch(int nErr3)
{
printf("Fail to import data to worksheet.\n");
return;
}
}
|
| additive |
Posted - 09/24/2010 : 10:00:23 AM
Hi CP,
that would be perfect, sounds great! Thanks a lot!
I'll do some more testing and hope I'll find a way so that we can finish the project on an 8.1 basis before migrating all the code to 8.5.
/ Michael
quote:
Originally posted by cpyang
So you basically want an option to allow default date column format to be specified such that OC code to PutRecordset can force date column (if has Date in the result) to be forced to that specified format, so you don't need to worry about destination or if contents might contain date.
There is already a POSTREADS structure added in 8.1 to allow nCodePage to be specified, so we can add another member to allow Date format to be specified as well.
We can add this but will have to be SR1 (Oct) as we are releasing 8.5.0 next week.
CP
|
| cpyang |
Posted - 09/23/2010 : 5:18:02 PM
So you basically want an option to allow default date column format to be specified such that OC code to PutRecordset can force date column (if has Date in the result) to be forced to that specified format, so you don't need to worry about destination or if contents might contain date.
There is already a POSTREADS structure added in 8.1 to allow nCodePage to be specified, so we can add another member to allow Date format to be specified as well.
We can add this but will have to be SR1 (Oct) as we are releasing 8.5.0 next week.
CP
|
| additive |
Posted - 09/23/2010 : 4:56:21 PM
Hi Easwar,
thank you for your quick answer! I will try it next week at the customers office. However, the problem with that workaround is that one has always to take care at which position in the SELECT statement the date is put ...
quote:
Originally posted by easwar
Hi Michael,
You could use a specific worksheet template where the column is set to desired date format, and then push data into that template?
OriginLab
Michael |
| easwar |
Posted - 09/23/2010 : 3:47:45 PM
Hi Michael,
You could use a specific worksheet template where the column is set to desired date format, and then push data into that template?
Easwar
OriginLab |
|
|
