« Calling a Stored Procedure Passing and Receiving a Date
Creating a SQL Database »

Fetching Date Values into DataFlex Date Field Using Embedded SQL

SQL Server databases has its own date format. This is a clean way to receive date columns from a statement (stmt) directly to a DataFlex (DF) Table.Field or a Date Variable.
Get_Attribute DF_DATE_FORMAT to iDateFormat_Stmt

  If (sDriver_Stmt = "MDSMYSQL") Begin
     If (iDateFormat_Stmt = DF_DATE_USA     ) Move "select code, name, date_format(date1,'%m/%d/%Y') from vi_5778 order by code" to sStmt
     If (iDateFormat_Stmt = DF_DATE_EUROPEAN) Move "select code, name, date_format(date1,'%d/%m/%Y') from vi_5778 order by code" to sStmt
     If (iDateFormat_Stmt = DF_DATE_MILITARY) Move "select code, name, date_format(date1,'%Y/%m/%d') from vi_5778 order by code" to sStmt
  End
  If (sDriver_Stmt = "MDSPGSQL") Begin
     If (iDateFormat_Stmt = DF_DATE_USA     ) Move "select code, name, RTrim(substring(date1 from 6 for 2) || '/' || substring(date1 from 9 for 2) || '/' || substring(date1 from 1 for 4)) as date1 from vi_5778 order by code" to sStmt
     If (iDateFormat_Stmt = DF_DATE_EUROPEAN) Move "select code, name, RTrim(substring(date1 from 9 for 2) || '/' || substring(date1 from 6 for 2) || '/' || substring(date1 from 1 for 4)) as date1 from vi_5778 order by code" to sStmt
     If (iDateFormat_Stmt = DF_DATE_MILITARY) Move "select code, name, RTrim(substring(date1 from 1 for 4) || '/' || substring(date1 from 6 for 2) || '/' || substring(date1 from 9 for 2)) as date1 from vi_5778 order by code" to sStmt
  End

Tip:

For Flex2SQL we introduced a special command which set it automaticly.
It is a Procedure Set named ‘SqlUseDataflexDateFormat’

Usage: 

Set SqlUseDataflexDateFormat to DFTrue/DFFalse

Tags:

This entry was posted on Monday, February 18th, 2008 at 3:50 pm and is filed under Code Examples. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply