Char to Military Time Conversion

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

General SQL Server Forums

New to SQL Server Programming

Char to Military Time Conversion

Author

Topic

dim
Yak Posting Veteran

57 Posts

Posted - 2010-09-21 : 15:18:02

Hi,

I have a database column which is of type varchar and gives values like 07:10a,06:55p,09:30p....etc

I need to covert these into Military Time Format like
07:10a -----should be 07:10
06:55p------should be 18:55

I am not able to use the standard conversion function as these values have a or p attached. Please let me know if there is a way to achieve this.

Thank you,

Dp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-21 : 15:25:29

Please do the formatting in the application and not in T-SQL. Formatting is best done by the presentation layer and not in the database.

Also, you should consider using proper data type going forward to avoid an issue like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 15:41:57

now this is just plain silly but...


DECLARE @t table(t varchar(10)); INSERT INTO @t(t) SELECT '06:55p' UNION ALL SELECT '06:55a' 
SELECT  CONVERT(varchar(25),
		CONVERT(datetime,
		  RIGHT('0'
		+ CONVERT(varchar(2),CONVERT(int,SUBSTRING(t,1,2)) 
		+ CASE WHEN SUBSTRING(t,6,1) = 'p' THEN 12 ELSE 0 END),2)
		+ SUBSTRING(t,3,3))
		, 108)
FROM @t

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

dim
Yak Posting Veteran

57 Posts

Posted - 2010-09-21 : 16:11:45

Hi X002548

I tried your code and I am able to get tge desired output with static values. But when I use the column ---I get the error message

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

My code is as follows:

SELECT substring (CONVERT(varchar(25),
CONVERT(datetime,
RIGHT('0'
+ CONVERT(varchar(2),CONVERT(int,SUBSTRING(dbo.Driver_Actual_Out,1,2))
+ CASE WHEN SUBSTRING(dbo.Driver_Actual_Out,6,1) = 'p' THEN 12 ELSE 0 END),2)
+ SUBSTRING(dbo.Driver_Actual_Out,3,3))
, 108), 1, 5)
from dbo.Driver

In the column I do not have any out of range time values ...it is very natural like 07:55p , 09:00a, 06:30p .....

Thank You,

Dp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-09-21 : 16:33:41

[code]declare @t table (t varchar(10));

insert into @t (t)
select '06:55p' union all
select '06:55a'

select
t,
[DateTime] = convert(datetime,t+'m')
from
@t
[/code]
Results:
[code](2 row(s) affected)

t DateTime
---------- -----------------------
06:55p 1900-01-01 18:55:00.000
06:55a 1900-01-01 06:55:00.000

(2 row(s) affected)[/code]

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 16:45:40

dude....there are no miracles

some of your data does not fit your model

run this...just replace the table and column name


DECLARE @t table(t varchar(10)); 
INSERT INTO @t(t) 
SELECT '06:55p' UNION ALL SELECT '06:55a' UNION ALL
SELECT '0X:55p' UNION ALL SELECT '06:55c' UNION ALL
SELECT '06:5Zp' 

SELECT t
	 , ISNUMERIC(SUBSTRING(t,1,2)), ISNUMERIC(SUBSTRING(t,4,2))
	 , SUBSTRING(t,6,1), SUBSTRING(t,3,1)
  FROM @t
 WHERE ISNUMERIC(SUBSTRING(t,1,2)) = 0
	OR ISNUMERIC(SUBSTRING(t,4,2)) = 0
	OR SUBSTRING(t,6,1) NOT IN ('a','p')
	OR SUBSTRING(t,3,1) <> ':'

dim
Yak Posting Veteran

57 Posts

Posted - 2010-09-21 : 17:04:34

Thank You I had spaces in the data and as a result I was getting the error. Applying the ltrim and case statement I was able to execute the above query.

Thank You,

Dp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources