how to convert char(14) field to dd/mm/yyyy hh:mm:ss

Try this:

select CAST( SUBSTR(YourField, 1, 4) + '-' + SUBSTR(YourField, 5, 2) + '-' + SUBSTR(YourField, 7, 2) + ' ' +
SUBSTR(YourField, 9, 2) + ':' + SUBSTR(YourField, 11, 2) + ':' + SUBSTR(YourField, 13, 2) AS DATETIME);

I Have two fields STARTTIME ENDTIME in char(14) as format yyyymmddhhmmss, how to calculate the elapsed time in hh:mm:ss?

Then you can use the DATEDIFF function:

select DATEDIFF( Minute ,
CAST( SUBSTR(YourField, 1, 4) + '-' + SUBSTR(YourField, 5, 2) + '-' + SUBSTR(YourField, 7, 2) + ' ' +
SUBSTR(YourField, 9, 2) + ':' + SUBSTR(YourField, 11, 2) + ':' + SUBSTR(YourField, 13, 2) AS DATETIME),
CAST( SUBSTR(YourField2, 1, 4) + '-' + SUBSTR(YourField2, 5, 2) + '-' + SUBSTR(YourField2, 7, 2) + ' ' +
SUBSTR(YourField2, 9, 2) + ':' + SUBSTR(YourField2, 11, 2) + ':' + SUBSTR(YourField2, 13, 2) AS DATETIME));

Depends on how the date is stored.

If it is stored as YYYYMMDDHHMISS (which is 14 characters) you use a few substrings. Similar to DaniPro exept you don't need Datediff and I think Cast is for MS-SQL not sybase (though it may be added in version 12).

But it the format is, as I said, YYYYMMDDHHMISS then use this:

select (convert (datetime,
   substring(datecolumn, 1, 8)  + ' '
+ substring(datecolumn,11, 2) + ':'
+ substring(datecolumn,13, 2) ))

CAST() was added to ASE as of 12.5.1

-bret

Thanks danipro I got it in seconds by doing datediff, how can i convert these seconds in to the HH:MM:SS format

Peraphs is a lot of complex, but the last field have the value you need:

select
(DATEDIFF( Second ,
CAST( SUBSTR(YourField, 1, 4) + '-' + SUBSTR(YourField, 5, 2) + '-' + SUBSTR(YourField, 7, 2) + ' ' +
SUBSTR(YourField, 9, 2) + ':' + SUBSTR(YourField, 11, 2) + ':' + SUBSTR(YourField, 13, 2) AS DATETIME),
CAST( SUBSTR(YourField2, 1, 4) + '-' + SUBSTR(YourField2, 5, 2) + '-' + SUBSTR(YourField2, 7, 2) + ' ' +
SUBSTR(YourField2, 9, 2) + ':' + SUBSTR(YourField2, 11, 2) + ':' + SUBSTR(YourField2, 13, 2) AS DATETIME))
) AS cf_diff, (cf_diff/3600) AS cf_hours, (cf_diff - (cf_hours * 3600)) / 60 AS cf_minutes,
cf_diff - ((cf_hours * 3600) + (cf_minutes * 60)) AS cf_seconds,
CAST( CAST(cf_hours AS char(2)) + ':' + CAST(cf_minutes AS char(2)) + ':' + CAST(cf_seconds AS char(2)) as time) AS ok ;

In my sybase version CAST funcation not there and it is complaining cf_diff is invalid column.
I have given the my sybase version

Adaptive Server Enterprise/11.9.2.1/1106/P/SWR 8738 ESD 4/HP9000-735/HP
-UX 10.2/FBO/Tue Nov 30 09:58:29 1999

Then you can use the CONVERT function:

select
(DATEDIFF( Second ,
CONVERT(DATETIME, SUBSTR(YourField, 1, 4) + '-' + SUBSTR(YourField, 5, 2) + '-' + SUBSTR(YourField, 7, 2) + ' ' +
SUBSTR(YourField, 9, 2) + ':' + SUBSTR(YourField, 11, 2) + ':' + SUBSTR(YourField, 13, 2)),
CONVERT(DATETIME, SUBSTR(YourField2, 1, 4) + '-' + SUBSTR(YourField2, 5, 2) + '-' + SUBSTR(YourField2, 7, 2) + ' ' +
SUBSTR(YourField2, 9, 2) + ':' + SUBSTR(YourField2, 11, 2) + ':' + SUBSTR(YourField2, 13, 2)))
) AS cf_diff, (cf_diff/3600) AS cf_hours, (cf_diff - (cf_hours * 3600)) / 60 AS cf_minutes,
cf_diff - ((cf_hours * 3600) + (cf_minutes * 60)) AS cf_seconds,
CONVERT(TIME, CONVERT(CHAR(2), cf_hours) + ':' + CONVERT(CHAR(2), cf_minutes) + ':' + CONVERT(CHAR(2), cf_seconds) ) AS ok ;

Line 1:
Invalid column name 'cf_diff'.
still i am getting above error

select convert(varchar, dateadd(ss,YourValueInSeconds,"01-jan-1900"),108)