Style Time Field to HH:MM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Style Time Field to HH:MM

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-02-23 : 14:36:40

Good day,

I need to format a time field.
I have a date split into two fields date & time
Date: 20070227
I got that going by: convert(nvarchar(10), cast(EHistTask.CreateDate as datetime), 103)

but now i would like to add the time to: HH:MM
Time: 144100
Result: 14:41

Please Help

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-23 : 14:45:28

Sine you haven't used datetime fields to store your dates, I think you will have to use string operations to get the time formatted.

It's best to do this in your front end, if you have one. Otherwise try this

declare @t int
select @t = 144100
select left(convert(varchar(10),@t),2) + ':' + substring(convert(varchar(10),@t),3,2)

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2010-02-24 : 02:08:43

Thank You - Great stuff

left(convert(varchar(10),EHistTask3.CreateTime),2) + ':' + substring(convert(varchar(10),EHistTask3.CreateTime),3,2)

:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 02:15:47


declare @t int
select @t = 94100
select left(convert(varchar(10),@t),2) + ':' + substring(convert(varchar(10),@t),3,2)

Result :
          
--------- 
94:10

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 02:16:28

quote:
Originally posted by ismailc

Good day,

I need to format a time field.
I have a date split into two fields date & time
Date: 20070227
I got that going by: convert(nvarchar(10), cast(EHistTask.CreateDate as datetime), 103)

but now i would like to add the time to: HH:MM
Time: 144100
Result: 14:41

Please Help

Why dont you use a proper DATETIME datatype to store the dates?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 02:19:10

[code]
select stuff(left(right('0' + convert(varchar(10), @t), 6), 4), 3, 0, ':')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources