ODBC - Retrieving Date information

[IanC74]

Hi

I have an ODBC connection from our Progress DB into SQL Server 2008.

The connection pulls data from the SQL database for use in the Progress environment.

The field in question is a DATETIME field - i.e. in SQL the format is stored as "25/01/2009 07:20:30"

I use the following progress statement to get this field data:

DISPLAY string(ObjRecordSet:FIELDS("Date"):VALUE).

Where "Date" is the name of the actual field in the SQL DB.

However, when retrieving the date - it only returns the date itself and not the full date and time stamp which I need. i.e. only shows "28/01/20009".

Does anyone know how I can get this to also pull back the time stamp part - im sure im doing something silly but I cant get my head around this.

Many thanks
Ian

 

[Stefan]

Never used ADO stuff before (we use the SQL DataServer), so found some ADO code on PEG (http://www.peg.com/forums/webspeed/200404/msg00124.html), the date time is returned fine.

I'm using 10.2B05, the SQL Native Client 10.0 and SQL Server 2008.

MESSAGE shows date time, defining a variable as DATETIME and then assigning the :Value to it correctly fills the time portion.

Are you simply having an issue with the default display format for characters being x(8)?

 

[IanC74]

Hi Stefan

Thanks for your reply.

I managed to figure out the problem, i had to use a different time convert format to allow me to pull time and date back. Its the SQL DB that was causing the issue.

However, using the following convert in my SQL SELECT statement resolved the issue:

convert(nvarchar(20), Date, 113) as Date

Ian

 

[Stefan]

For my test I did not do any converting and simply took the value...

The relevant parts:

           ODBC-QUERY  = "SELECT * FROM admdat WHERE adm_nr = 621".

          MESSAGE 
            ObjRecordSet:Fields ("adm_nr"):Value ","
            ObjRecordSet:Fields ("date_lc"):VALUE "," SKIP(1)
             dt
            SKIP VIEW-AS ALERT-BOX.