Solved

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

Posted on 2004-03-23
14
5,049 Views
Last Modified: 2012-06-27
How to convert char field of length 14 to the format dd/mm/yyyy hh:mm:ss in sybase.
Please help me some one.

Thanks
0
Comment
Question by:janum
14 Comments
 
LVL 6

Expert Comment

by:DaniPro
ID: 10665227
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);
0
 

Author Comment

by:janum
ID: 10665480
I Have two fields STARTTIME ENDTIME in char(14) as format yyyymmddhhmmss, how to calculate the elapsed time in hh:mm:ss?
0
 
LVL 6

Expert Comment

by:DaniPro
ID: 10665618
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));
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 3

Expert Comment

by:ahoor
ID: 10666309
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) ))

0
 
LVL 10

Expert Comment

by:bret
ID: 10672906
CAST() was added to ASE as of 12.5.1

-bret
0
 

Author Comment

by:janum
ID: 10674511
Thanks danipro I got it in seconds by doing datediff, how can i convert these seconds in to the HH:MM:SS format
0
 
LVL 6

Expert Comment

by:DaniPro
ID: 10675099
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 ;
0
 

Author Comment

by:janum
ID: 10675262
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
0
 
LVL 6

Expert Comment

by:DaniPro
ID: 10675330
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 ;
0
 

Author Comment

by:janum
ID: 10675384
Line 1:
Invalid column name 'cf_diff'.
still i am getting above error
0
 
LVL 6

Accepted Solution

by:
DaniPro earned 125 total points
ID: 10675428
Ok, i'm using ASA that use aliases different than ASE. Try this SQL, is simpler and shuold be good:

select
DATEADD( Second,
(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)))
) , DATE('1900-01-01'));
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11344785
select convert(varchar, dateadd(ss,YourValueInSeconds,"01-jan-1900"),108)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Binary Differential Replication, What it is, how it works and how it differs from standard delta file replication
SEO can be a real minefield to navigate, but there are three simple ways to up your SEO game just be re-assessing your content output.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question