Format time

Hi,
I have Int columt the vlue like 1330 .
I need to conver to time , the result like 1:30 PM
Thanks
samprgAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
or better

declare @myVar int = 900
select cast((@myVar / 100) % 12 as varchar ) + ':' + right('00' + cast(@myVar %100 as varchar),2) + ' ' +
case when (@myVar / 100) > 12 then 'PM' else 'AM' end

9:00 AM
0
 
Christopher GordonSenior Developer AnalystCommented:
If your number represents 13:30, you can try this:

declare @myVar int = 1130

select right(convert(nvarchar(30), cast('1/1/1900 ' + stuff(@myVar,3,0,':') as datetime), 100),7)

Open in new window

0
 
samprgAuthor Commented:
good,
but If I put 900, gives me erroe.
Msg 242, Level 16, State 3, Line 10
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Christopher GordonSenior Developer AnalystCommented:
what should 900 represent 9:00?  If so, make this modification.
declare @myVar int = 900

select right(convert(nvarchar(30), cast('1/1/00 ' + stuff(right('0' + cast(@myVar as nvarchar(4)),4),3,0,':') as datetime), 100),7)

Open in new window

0
 
samprgAuthor Commented:
I got 3 rows then gives me error
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type datetime.
0
 
Christopher GordonSenior Developer AnalystCommented:
Hmmm... yeah, I see a problem.

How would you represent 12:01 AM as an integer in your above example?
0
 
Christopher GordonSenior Developer AnalystCommented:
Also, do you have any values > 2359?
0
 
Christopher GordonSenior Developer AnalystCommented:
I'm also assuming that you can't use the DateTime and SmallDateTime type fields for some reason.  Storing time like this via an integer is less than optimal.
0
 
HainKurtSr. System AnalystCommented:
what about this

declare @myVar int = 1330
select cast((@myVar / 100) % 12 as varchar ) + ':' + cast(@myVar %100 as varchar) + ' ' +
case when (@myVar / 100) > 12 then 'PM' else 'AM' end
0
 
Christopher GordonSenior Developer AnalystCommented:
Another thought to help potentially troubleshoot issues with this...

Do you get any results for this query (replace myTimeIntField)

select [myTimeIntField]
right(CAST([myTimeIntField] as varchar(4)),2) > 59

This would catch an int value like this 980, which wouldn't be a valid time (i would assume)
0
 
samprgAuthor Commented:
Awesome
0
 
Christopher GordonSenior Developer AnalystCommented:
Just a "heads up" that the following input will create 9:89 AM.

declare @myVar int = 989

select cast((@myVar / 100) % 12 as varchar ) + ':' + right('00' + cast(@myVar %100 as varchar),2) + ' ' +
case when (@myVar / 100) > 12 then 'PM' else 'AM' end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.