1 min read

Fetching Date Values into DataFlex Date Field Using Embedded SQL

Picture of Mertech Mertech : Feb 18, 2008 3:50:00 PM

DataFlex

SQL Server databases have a unique 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 from1 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 automatically.
It is a Procedure Set named 'SqlUseDataflexDateFormat'

Usage:

Set SqlUseDataflexDateFormat to DFTrue/DFFalse