SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to convert military time to standard time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  15:28:53  Show Profile  Reply with Quote
How to convert military time to standard time

15:12:47 to 2:12:47 PM?

9:13:33 to 9:13:33 AM?

lmnorms
Starting Member

8 Posts

Posted - 03/06/2009 :  15:38:14  Show Profile  Reply with Quote
Hello,
Military time is also called 24 hour time. From 00:00:01 to 24:00:00.
If the time is less than 12:00:00 just use clock time. But from 12:59:59 forward the time should take the clock time and add 12 hours. For 1:00 pm it would convert to 13:00 hrs. To get the time from 'Military' time subtract 12 hours. 14:12:47 is 2:12:47pm.

Hope the information helps.
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  15:40:47  Show Profile  Reply with Quote
do u have any script i will test?
Go to Top of Page

lmnorms
Starting Member

8 Posts

Posted - 03/06/2009 :  15:41:52  Show Profile  Reply with Quote
Actually, I think I have a vb script has an example if that is ok.
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  15:42:55  Show Profile  Reply with Quote
let me try
Go to Top of Page

lmnorms
Starting Member

8 Posts

Posted - 03/06/2009 :  15:47:18  Show Profile  Reply with Quote
Here are some functions. If you need something else let me know.

' convert to a string in military time
Dim input As String = DateTime.Now.ToString(militaryTimeFormat)

' convert from a string in military time
Dim time As DateTime = DateTime.ParseExact(input,
militaryTimeFormat, Nothing)
Go to Top of Page

lmnorms
Starting Member

8 Posts

Posted - 03/06/2009 :  15:47:47  Show Profile  Reply with Quote
Here are some functions. If you need something else let me know.

' convert to a string in military time
Dim input As String = DateTime.Now.ToString(militaryTimeFormat)

' convert from a string in military time
Dim time As DateTime = DateTime.ParseExact(input,
militaryTimeFormat, Nothing)
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  15:50:44  Show Profile  Reply with Quote
i don't think so its gonna implement on sql script?
ParseExact is your own function??

Edited by - rudba on 03/06/2009 15:51:20
Go to Top of Page

lmnorms
Starting Member

8 Posts

Posted - 03/06/2009 :  15:53:57  Show Profile  Reply with Quote
No. These are vb functions. You should have access to similar sql functions that retrieve time. Tell me a little more about the situation. How, and why do you need to convert?
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  16:07:01  Show Profile  Reply with Quote
it does not implement on T-SQL.
guys any body have idea?
Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 03/06/2009 :  16:08:05  Show Profile  Reply with Quote
IS it a datetime field that you are trying to convert?
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  16:10:05  Show Profile  Reply with Quote
yes its datetime field.
i don't need date. Just need to convert time.
Like this:
15:12:47 to 3:12:47 PM?
9:13:33 to 9:13:33 AM?


Edited by - rudba on 03/06/2009 16:13:34
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  16:33:45  Show Profile  Reply with Quote
vijayisonly, did u get?
quote:
Originally posted by vijayisonly

IS it a datetime field that you are trying to convert?

Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 03/06/2009 :  16:36:28  Show Profile  Reply with Quote
Try this...

SELECT LTRIM(RIGHT(CONVERT(char,urcol,109),18)) from urtable
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  16:43:05  Show Profile  Reply with Quote
thanks vijayisonly. good job
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 03/06/2009 :  16:43:09  Show Profile  Reply with Quote
109 will return you milliseconds as well. Try this,

select right(convert(varchar, getdate(),0),7)
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  16:52:30  Show Profile  Reply with Quote
Yes its return milliseconds too. I don't need milliseconds.

I tried this but get err
SELECT LTRIM(RIGHT(CONVERT(char,mycol,09),7)) from mytbl
Go to Top of Page

sakets_2000
Flowing Fount of Yak Knowledge

India
1472 Posts

Posted - 03/06/2009 :  16:59:30  Show Profile  Reply with Quote
SELECT  right(convert(varchar, mycol,0),7) from mytbl

Edited by - sakets_2000 on 03/06/2009 17:02:04
Go to Top of Page

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 03/06/2009 :  17:13:28  Show Profile  Reply with Quote
how to get seconds
quote:
Originally posted by sakets_2000

SELECT  right(convert(varchar, mycol,0),7) from mytbl


Go to Top of Page

vijayisonly
Flowing Fount of Yak Knowledge

USA
1836 Posts

Posted - 03/06/2009 :  17:58:47  Show Profile  Reply with Quote
Do this...

SELECT REPLACE(LTRIM(RIGHT(CONVERT(char,urcol,109),18)),':000',' ') from urtable
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000