May 6, 2011 at 1:03 pm
Hi,
I need to convert a datetime field from military time to standard time. I also need to do a datepart to get
just the hour from the time.
I tried using this but I am still getting a military time format.
convert(varchar, datefield, 8)
The results are : 15:30.00, 16:00:00.
I bascially need to get the hour stripped out so my results are like this: 9,10,11,12,1,2,3,4 instead of
9,10,11,12,13,14,
any suggestions
Thanks
May 6, 2011 at 2:13 pm
What do you want to do for midnight? Do you want a single 0 for the hour? If you want a 0 for midnight then this would work:
SELECT
CASE
WHEN DATEPART(Hour, CURRENT_TIMESTAMP) > 12 THEN DATEPART(hour, CURRENT_TIMESTAMP) - 12
ELSE DATEPART(Hour, CURRENT_TIMESTAMP)
END AS the_time
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2011 at 2:21 pm
What is the source datatype that you're extracting the information from? DATETIME?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 6, 2011 at 2:43 pm
Yes, the field is a datetime field. 2009-07-15 17:30:00.000
May 8, 2011 at 11:17 pm
Marv2011 (5/6/2011)
Yes, the field is a datetime field. 2009-07-15 17:30:00.000
Try out this ..
create table #temp_table
( a datetime)
go
insert into #temp_table (a) values (getdate())
insert into #temp_table (a) values (getdate()+.10)
insert into #temp_table (a) values (getdate()+.09)
go
select Case when datepart(hour, a)>12 then datepart(hour,a) -12
else datepart(hour,a)
End as 'Standard Hour'
from #temp_table
go
drop table #temp_table
Regards,
Sachin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply