Solved

Format time

Posted on 2011-09-13
12
216 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 51

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 51

Accepted Solution

by:
HainKurt 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

825 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