Convert time as int to a datetime value

[dwinn]

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

[debasisdas]

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

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

[dwinn]

Hi debasisdas,

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

[debasisdas]

Is the format consistent all across ?

[dwinn]

Yes it is.

Can I use the convert() function on it?

[debasisdas]

What about date part ,are you going to use current date fro that ?

[dwinn]

Sorry,

Am I going to use current date for what?

[debasisdas]

I think you said , you wanted to Convert time into a datetime, right ?

[dwinn]

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?

[debasisdas]

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.

[dwinn]

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

[adam_k]

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

[debasisdas]

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.

[dwinn]

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

[debasisdas]

What format your time is in ?

Is it 12 Hrs or 24 Hrs format.

[dwinn]

Hello debasisdas,

I believe my times are in 24 hour format.

Dan

[debasisdas]

No problem them.

What about the numbers that stores dates, are those always in specific format ?

[dwinn]

The dates are now as: dd/mm/yyyy

Dan

[adam_k]

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.