Solved

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

Posted on 2004-03-23
14
5,070 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Return results from Sybase DB Query with Powershell 7 1,738
Sybase sample database 13 723
SIMPLE QUESTION BACKUP AND RESTORE ORACLE DATABASE 13 95
sql statement error sybase 2 64
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime is disastrous for companies and can lead to major hits on a brand, reputation, an…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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