Convert Decimal Time Value to Military Time Value

Hello – I have a SQL 2008 R2 Database that stores a Start Time as a Decimal (4,0) value.  The output appears as:  700, 1100, 1330 etc.

I want to display the time as 0700 AM, 1100 AM, 1330 PM etc.

I attempted to convert the data type but run into all sorts of errors.  I have found several posts regarding converting a time value to decimals but not the other way around.  

Since I am going to use this information in a report, I would prefer to create a SQL view that will contact the data fields I need.  I need help with the time conversion please.  The Date field is a DateTime field, so that is less painful.  

Who is Participating?
Anthony PerkinsCommented:
Something like this perhaps:
SELECT RIGHT(CONVERT(varchar(20), DATEADD(minute, @YourDecimalValue / 100 + @YourDecimalValue % 100, '19000101'), 0), 7)

Open in new window

Scott CraigWebmasterCommented:
So, just to confirm, the data is stored as 13.50 for 13:30 and you want it displayed as 13:30 PM, correct?
This will give you a DateTime with today's date and the time component specified.  The ToString("0000") is simply padding with leading zeros.

      static void Main( string[] args )
         decimal timeAsDecimal = 700.0m;
         DateTime parsedTime = DateTime.ParseExact( timeAsDecimal.ToString( "0000" ), "hhmm", null );
         Console.WriteLine( parsedTime );

Open in new window

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.

SQLwatcherAuthor Commented:
Hi ...well actually the date does not have a decimal in it however the field type in SQL is decimal (4,0).
The code you provided is JAVA, correct?  I may not be able to conver that in a SQL View.?  
This is a function I use with int values.  should work fine with DECIMAL(4,0)

CREATE FUNCTION [dbo].[IntToTime] (@TimeValue int)
RETURNS datetime AS  
Declare @Hour char(2)
Declare @Min char(2)

Set @Hour = substring(cast(100 + Cast ((@TimeValue / 100) as int) as char(3)),2,2)
set @Min = substring(cast(100 + Cast ((@TimeValue % 100) as int) as char(3)),2,2)

Return ( cast(@Hour + ':' + @min  as datetime))
SQLwatcherAuthor Commented:
Thank you for the help.  Sorry for getting back to you so late.  :o(
SQLwatcherAuthor Commented:
Thank You!
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.