Solved

Format time

Posted on 2011-09-13
12
214 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now