Solved

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

Posted on 2004-03-23
14
5,060 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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: 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 2 456
SQL Query Syntax 17 164
MSSQL Error: 9002, Severity: 17, State: 2. MSQLSERVER Process shuts off 9 508
Why did SAP buy Sybase? 3 168
Is your phone running out of space to hold pictures?  This article will show you quick tips on how to solve this problem.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

792 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