May 18, 2008 at 6:51 am
I have a field that has a varchar of data that represents the time as example ( 9:34 , 10:10 , 22:00). I want to convert it to look like (0934, 1010, 2200) so that it is a character .
May 18, 2008 at 8:40 am
always comming to the same problem, when not storing a column according to its natural datatype. :doze:
Declare @test-2 varchar(15)
set @test-2 = '9:08'
select REPLICATE('0',4 - datalength(replace(@test ,':','') )) + replace(@test ,':','')
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 28, 2008 at 6:44 pm
looked like a clever solution, so I tried it. where do you propose this be run?
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
May 29, 2008 at 2:52 pm
Assuming this is Oracle because written in _Oracle_ category in SQL Server forums, so:
SQL> with a as (
2 select '9:34' col from dual
3 union all
4 select '10:10' from dual
5 union all
6 select '22:00' from dual)
7 select to_char(to_date(col, 'fmHH24:MI'), 'fxHH24MI') from a
8 /
TO_C
----
0934
1010
2200
Gints Plivna
P.S. And yes I agree storing dates (datetimes, numbers etc having special types) in varchar columns is bad practice.
Gints Plivna
http://www.gplivna.eu
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply