0

Hello,

I am currently working on a stored procedure that shows the time of day as an integer value: 456, which means 4:56.

I was wondering if there is a way in SQL that I can convert this int in to a datetime value.

So instead of the time appearing as 456, it actually shows up as 4:56.

Can anybody advise on this at all?

Many thanks,

Dan

3
Contributors
18
Replies
19
Views
6 Years
Discussion Span
Last Post by adam_k
0

I don't see any relation between 456 and 4:56.


I you wan to separate, can simply split using a delimeter.

0

Hi debasisdas,

I am trying to convert it so it appears as a time value instead of a number.

0

Yes it is.

Can I use the convert() function on it?

0

Sorry,

Am I going to use current date for what?

0

Yes,

So where at the moment I am dealing with times such as 456 and 231, I wish to convert them so they read as 4:56 and 2:31.

Do you get what I mean?

0

That i understand,

what i meant is datatime has 2 parts (date + time).

you got the time part by formatting the number, i was asking about date part.

Once you have complete date with time that can be used for some calculations in the DB.

0

Sorry, I get what you mean now.

Ive got two fields in the database

- DateOfManufacture
- TimeOfManufacture

The date originally looked like: 9092011

The date I have converted to look like dd/mm/yyyy.

I am now trying to change the time so I can combine the 2 fields together to make date and time.

For example: 09/09/2011 4:56

But first I am trying to work out how to change the format of the time.

Many thanks,

Dan

0

To change the time, you can check the length and determine where to insert the :
try this

select 
case when len(time_field) = 4 then convert(char(2),left(time_field,2)) + ':' + convert(char(2),right(time_field,2))
when len(time_field) = 3  then convert(1),left(time_field,1)) + ':' + convert(char(2),right(time_field,2))
from table

Edited by adam_k: n/a

0

if the format is consistent all across then it can be easily converted.

Is there any instance where date is stored as both

9092011 and 09092011

the first format is going to create problem.

0

Adam_k,

Thank you very much it worked.

One last question...is there a way to add a 0 to the beginning of times that are only 3 digits.

For example...where I have say 8:14 or 3:16, is there a way to make it appear as 08:14 or 03:16 at all?

Thanks,

Dan

0

Hello debasisdas,

I believe my times are in 24 hour format.

Dan

0

If you can handle the dates then adding the zero shouldn't be a problem, but in any case, here you go:

SELECT case when len(time_field) = 4 then convert(char(2),LEFT(time_field,2)) + ':' + convert(char(2),RIGHT(time_field,2))
when len(time_field) = 3  then convert(char(1),'0') + convert(1),LEFT(time_field,1)) + ':' + convert(char(2),RIGHT(time_field,2))
end as 'time'
FROM TABLE

Also seems I forgot to end the case in my previous post.

Edited by adam_k: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.