Solved

Format time

Posted on 2011-09-13
12
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 52

Expert Comment

by:Huseyin KAHRAMAN
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 52

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query group by data in SQL Server - cursor? 3 66
Negative isnull? 3 25
SSIS Standard Template for Reuse by Business Units 12 54
Please explain Equi-join 3 17
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, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

696 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