Convert Time to 24 Hour Clock

All ,

I need to convert the given time into 24 hour clock .

I have the two tables out of which one contain time in 12 Hr clock and another contain time in 24 Hr clock and i need to make a join on this colum by converting 12 Hr time in to 24 hr clock time.

Please help ..

Regards,

Ashish

try this one..

SELECT CONVERT(varchar,GETDATE(),114), CONVERT(varchar,convert(smalldatetime,'1/1/2007 7:00:00 PM'),114)|||

Ashish:

What exactly do you mean by "... one contain time in 12 Hr clock ..." what is the datatype of that particular column? The standard SQL Server "DATETIME" datatype stores data such that if you specify incoming time with a 12 Hr clock going in it is still be stored in the same internal way as time that is specified incoming with a 24 hour clock. If both columns are stored as DATETIME datatypes you can compare them directly without needing any conversion.

Kent

|||

Hi Kent ,

Here is the content of the two table and both data types are varchar

Table 1 Table 2

Col Col

12.00a 1200

1.00p 1300

2.30p 1430

Now the problem is i can directly add 12 to the table 1 values by taking substring and converting it into integer but here i dont have 00.00 for 12 Am rather i have 12.00a for the same so if i add 12 to 12.00a it became 24.

I am not able to get if i can use case in where but seems it is not possible , Do you know any other way to do it.

Regards,

Ashish

|||

Ashish,

In order for us to provide you the best suggestions, please post the DDL for both tables.

|||

( Have you got this one then, Arnie? Thanks, Arnie; I'll go ahead with it then.)

Question: How does 12:30 AM appear for table 2 (24 hour clock)

Also, you show '12.00a' on the 12 hour clock translates to '1200' will you please verify this? This looks to me like an error.

|||Nope, just thought the question should be asked.|||

Code Snippet

declare @.table1 table (time1 varchar(6))
declare @.table2 table (time2 varchar(4))
insert into @.table1
select '12.00a' union all
select '12.30a' union all
select '1.00p' union all
select '2.30p'
--select * from @.table1

insert into @.table2
select '0000' union all
select '0030' union all
select '1300' union all
select '1430'
--select * from @.table2

select a.time1,
b.time2
from ( select case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)
as time1
from @.table1
) a
join @.table2 b
on a.time1 = cast(b.time2 as integer)

/*
time1 time2
-- --
0 0000
30 0030
1300 1300
1430 1430
*/

Now, there is another piece of this. And that is that both of these are going to table scan as they are because indexes cannot be employed for lookups. What you REALLY need to consider is converting these tables so that the time values get stored in a standard fashion -- either as an integer or as a datetime datatype. This will give the optimizer an optimizer to employ indexes if they are available. And if you are frequently going to join these two tables based on time then you really should try to set it up so that this stuff gets indexed with a standard datatype.

(I don't think I need these reverses, but I don't have time to eliminate them either. Will somebody either check me on this or fix this?)

|||

I didn't even get out of the parking lot and I realized that the PARSENAME was just too much baloney. Hang on and I'll rectify. Something like this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
cast((100*convert(numeric(7,2), reverse(substring(reverse(time1), 2, 5))))%1200 as integer)

is better than this:

case when right(time1, 1) = 'p' then 1200 else 0 end +
100*(cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 2),2) as tinyint)%12) +
cast(left(parsename(reverse(substring(reverse(time1), 2, 5)), 1),2) as tinyint)

|||

Here are the values in first table

ID Begin End

Time Time

1 12:00a 12:29a 2 12:30a 12:59a 3 1:00a 1:29a 4 1:30a 1:59a 5 2:00a 2:29a 6 2:30a 2:59a 7 3:00a 3:29a 8 3:30a 3:59a 9 4:00a 4:29a 10 4:30a 4:59a 11 5:00a 5:29a

Here is the Entries in second table

Begin Time

133502 133522 133502 133522 123502 123522 123502 123522 123504 123524 123505 123524 123502 123522 133522 123522 133522 124837

I need to compare Begin Time of first table to Second table and make a join

|||

I think that I see where this is going...

The next part of the problem will be how to group Table2 in the groups of Table1. (Every half-hour.) This is NOT going to stop with a simple join between disparate column datatypes...

**********************************************************************************

>>>> Ashish <<<-

It would be so much easier if you could change the datatypes for both tables to be shortdatetime datatypes.

Is that possible?

Otherwise, you are going to be saddled with incredible 'kludgy' code, and it will be increasingly difficult to create the kinds of information that you want to create from your data. Yes, we can help you hammer out some tortured code that will solve the current problem, BUT I don't think that you have fully explained where this is going. Get a grip and do it right -change the datatypes, then the solutions will be relatively simple! This is crazy making stuff and it is not going to get any better.

|||

Hi Arnie ,

I did either way around , Added one more column in the table and updated it with the corresponding conversion. I guess this will not increase any performance issue.

Your Thoughts....

Regards,

Ashish

|||

Ashish,

Thanks, that will greatly improve performance issues for handling the data in either a JOIN or an aggregation operation.

In your original post, you indicated that you wanted to JOIN the two tables. But your sample data does not indicate any columns with matching time values.

So is it a JOIN, or as I expressed earlier, are you really wanting to group by half-hour intervals -or something else?

Please clarify your desired results, and we can help you find a fast and efficient solution.