Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

Help cahanging existind data in a time field


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old April 1st, 2011, 11:03 AM
rmontz rmontz is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2009
Posts: 5 rmontz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 1 m 51 sec
Reputation Power: 0
Help cahanging existind data in a time field
I have an existing database/table and some records in the time field or H:M:SS format while others are HH:MM:SS format. I have corrected the issue causing this however need to convert all to H:M:SS (6:1:28) to HH:MM:SS(06:01:28)

Table name = TABLE1,TABLE2,TABLE3 (they all have a time field and all need to get converted)

Field name = TIME
Any Suggestions
Reply With Quote
  #2  
Old April 5th, 2011, 02:26 AM
mIRCata mIRCata is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2006
Location: Plovdiv. Bulgaria
Posts: 200 mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 2 Days 6 h 50 m 14 sec
Reputation Power: 11
Quote:
Originally Posted by rmontz
I have an existing database/table and some records in the time field or H:M:SS format while others are HH:MM:SS format. I have corrected the issue causing this however need to convert all to H:M:SS (6:1:28) to HH:MM:SS(06:01:28)

Table name = TABLE1,TABLE2,TABLE3 (they all have a time field and all need to get converted)

Field name = TIME
Any Suggestions

Is the fields' type TIME, or it's char/varchar? I guess that they are char/varchar, am I correct?
Last edited by mIRCata : April 5th, 2011 at 02:54 AM.
Reply With Quote
  #3  
Old April 5th, 2011, 09:02 AM
rmontz rmontz is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2009
Posts: 5 rmontz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 1 m 51 sec
Reputation Power: 0
Quote:
Originally Posted by mIRCata
Is the fields' type TIME, or it's char/varchar? I guess that they are char/varchar, am I correct?
yes it is (var text) varchar(8)
Reply With Quote
  #4  
Old April 6th, 2011, 03:03 AM
mIRCata mIRCata is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Sep 2006
Location: Plovdiv. Bulgaria
Posts: 200 mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level)mIRCata User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 2 Days 6 h 50 m 14 sec
Reputation Power: 11
Quote:
Originally Posted by rmontz
yes it is (var text) varchar(8)

And what is the reason for not using type TIME for these fields? It's 4 byte(you'll save space) and you won't have these problems.

Try this and see does it help you
Code:
UPDATE TABLE1 SET 
FIELD1 = LPAD(SUBSTRING(FIELD1 FROM 1 FOR POSITION(':', FIELD1) - 1), 2, '0') || ':' ||
LPAD(SUBSTRING(FIELD1 FROM POSITION(':',FIELD1) + 1 FOR POSITION(':', FIELD1, POSITION(':', FIELD1)) - 1), 2,'0') || ':' ||
LPAD(SUBSTRING(FIEDL1 FROM POSITION(':',FIELD1,POSITION(':', FIELD1) + 1) + 1),2,'0') FROM     TABLE1
WHERE
CHAR_LENGTH(FIELD1) < 8
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Help cahanging existind data in a time field

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap