Convert Time - 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 2008 Forums

Transact-SQL (2008)

Convert Time

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 21:22:06

Here is my question I have source data like this
Column_Name(Time)
1425
945
1258
And target field is Time
How I can change Military time to normal time
Time
2:25 AM
9:45 PM
12:58 AM
Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-05 : 21:28:24

what is the data type of the time column ?

KH
[spoiler]Time is always against us[/spoiler]

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 21:33:00

SOURCE COLUMN DATATYPE IS "INT" AND TARGET COLUMN DATATYPE IS "VARCHAR"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-05 : 21:35:21

why do you want to keep the time as varchar ? why don't you use the time data type ?


select	tm, convert(time, dateadd(second minute, (tm / 100) * 60 + tm % 100, 0))
from	yourtable

KH
[spoiler]Time is always against us[/spoiler]

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 21:42:26

I can change my source field but i can't change my target database. After i change my source field from int to Varchar, Here is my update statement
UPDATE MYTABLE
SET TIME = CONVERT(TIME,DATEADD(SECOND,(TM/100) * 60 + TM % 100, 0))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-05 : 21:52:04

you can leave the source in integer.

To convert result to varchar


convert(varchar(5), dateadd(minute, (tm / 100) * 60 + tm % 100, 0), 108)

KH
[spoiler]Time is always against us[/spoiler]

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 21:58:21

Thanks for your prompt reply,
Here is my sample table
CREATE TABLE TIME
(
ID INT,
T_TIME VARCHAR(10)
)

INSERT INTO TIME
VALUES ('1','1842')
GO
INSERT INTO TIME
VALUES ('2','845')
GO
INSERT INTO TIME
VALUES ('3','1245')
GO

after i run update statement
UPDATE TIME
SET T_TIME = convert(varchar(5), dateadd(minute, (T_TIME / 100) * 60 + T_TIME % 100, 0), 108)

I am getting this result

ID TIME
1 12:45
2 08:45
3 18:42

IT SHOULD BE

ID TIME
1 12:45 AM
2 08:45 PM
3 06:45 AM

Please guide me, Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-05 : 22:15:19

stuff(right('0' + ltrim(right(convert(varchar(30), dateadd(minute, (tm / 100) * 60 + tm % 100, 0), 0), 7)), 7), 6, 0, ' ')

KH
[spoiler]Time is always against us[/spoiler]

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 22:22:38

update TIME
set T_TIME = stuff(right('0' + ltrim(right(convert(varchar(30), dateadd(minute, (T_TIME / 100) * 60 + T_TIME % 100, 0), 0), 7)), 7), 6, 0, ' ')

I am getting this error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '12:45' to data type int.

?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-05 : 22:28:54

oh . .your data type is now string not integer ?


STUFF(RIGHT('0' + CONVERT(VARCHAR(30), CONVERT(TIME, STUFF(RIGHT('0' + T_TIME, 4), 3, 0, ':')), 0), 7), 6, 0, ' ')

KH
[spoiler]Time is always against us[/spoiler]

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-05 : 22:34:15

Awesome, works fine. Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources