Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Format time

Posted on 2011-09-13
12
Medium Priority
?
229 Views
Last Modified: 2012-08-14
Hi,
I have Int columt the vlue like 1330 .
I need to conver to time , the result like 1:30 PM
Thanks
0
Comment
Question by:samprg
  • 7
  • 3
  • 2
12 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532036
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
 

Author Comment

by:samprg
ID: 36532075
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532089
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:samprg
ID: 36532131
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532153
Hmmm... yeah, I see a problem.

How would you represent 12:01 AM as an integer in your above example?
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532165
Also, do you have any values > 2359?
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532192
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 36532219
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
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36532230
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532312
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
 

Author Closing Comment

by:samprg
ID: 36532419
Awesome
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 36532451
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question