24hr time question - 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 2000 Forums

SQL Server Development (2000)

24hr time question

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-10-03 : 23:07:39

I need to return time in 12 hour format a lot. I mostly use ASP for display and this does a poor job with time values. Is there an easy way or does someone have a good function to return 12 hour time from a datetime, smalldatetime value.

Thanks

slow down to move faster...

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-04 : 02:54:51

use style 109 with the convert function :


declare @ret datetime
select @ret = '2002-10-04 19:56:20.907' /*datetime in 24 hour format*/
select convert(varchar(28), @ret, 109)

skillile
Posting Yak Master

208 Posts

Posted - 2002-10-04 : 08:11:54

Yeah, thanks but I don't want
Oct 4 2002 7:56:20:907PM

but I do want,
7:56 PM (NOT 17:56)

Just the time. If i do hours, min, sec .... then do a case on the hour to subtract 12. That is the best I can come up with to show 12 hour format is there an easier way?

slow down to move faster...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-04 : 08:23:04

Here's a silly solution...
select reverse(left(reverse(convert(varchar,getdate())), charindex(' ',reverse(convert(varchar,getdate())))-1))

Jay White
{0}

skillile
Posting Yak Master

208 Posts

Posted - 2002-10-04 : 08:27:39

skillile
Posting Yak Master

208 Posts

Posted - 2002-10-04 : 08:29:00

sorry hit the refresh

slow down to move faster...

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-10-04 : 09:26:26

This works:


set nocount on

declare @date datetime

select @date = '2002-10-04 09:15'
select TheTime = Right(convert(char(19), @date , 100), 7)

select @date = '2002-10-04 23:35'
select TheTime = Right(convert(char(19), @date , 100), 7)

print ''
print '** put a space between the last digit and AM/PM **'
select @date = '2002-10-04 23:35'
select TheTime = Left(Right(convert(char(19), @date , 100), 7), 5)
                 + ' ' 
                 + Right(Right(convert(char(19), @date , 100), 7), 2)

/*** here are the results

TheTime 
------- 
 9:15AM

TheTime 
------- 
11:35PM

 
** put a space between the last digit and AM/PM **
TheTime  
-------- 
11:35 PM



****/

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-07 : 04:36:37

this will also do the job...


declare @ret datetime
select @ret = '2002-10-04 19:56:20.907'
select substring(convert(varchar (28),@ret,100),13,8)+' '+
 substring(convert(varchar(28),@ret,100),25,2)

it returns : 7:56PM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources