Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting Military Time to Standard time Expand / Collapse
Author
Message
Posted Friday, May 6, 2011 1:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 15, 2013 9:47 AM
Points: 49, Visits: 163
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
Post #1104840
Posted Friday, May 6, 2011 2:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1104878
Posted Friday, May 6, 2011 2:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 6,157, Visits: 7,213
What is the source datatype that you're extracting the information from? DATETIME?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1104887
Posted Friday, May 6, 2011 2:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 15, 2013 9:47 AM
Points: 49, Visits: 163
Yes, the field is a datetime field. 2009-07-15 17:30:00.000
Post #1104905
Posted Sunday, May 8, 2011 11:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:12 AM
Points: 159, Visits: 489
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
Post #1105205
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse