Solved

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

Posted on 2004-03-23
14
5,083 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The conference as a whole was very interesting, although if one has to make a choice between this one and some others, you may want to check out the others.  This conference is aimed mainly at government agencies.  So it addresses the various compli…
Here's how to start interacting with our community through Post.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 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