I have a table, one column of which is a TIme(0) datatype.
A query such as
||select max(race_time)|
|---|---|
||from TodaysRaces|
||where RACE_DATE=cast(getdate()as date)|
produces the result 12:52:00
A query such as
select min(race_time)
from TodaysRaces
where RACE_DATE=cast(getdate()as date)
Produces the result 01:27:00
I was expecting 12:52:00 to be the result of the min query, and not the result of the max query. What do I have to do for this to be the case?
Thanks for any help.
hi hope this helps
Select race_date ,min(race_time) ,max(race_time) from table123 where race_date = cast(getdate()as date) group by race_date = cast(getdate()as date)
Thanks for that, but its not really doing what I want. I am looking for a query or something that will recognise that 12:52 is earlier than 01:27, that is, that 12:52 is the earliest time in the table and 06:00 the latest time. If the list is ordered, either asc or desc, then 12:52 and 01:27 are always at opposite ends of the list. I need a way for the routine to recognise the 24 hour clock so that any value over 12:59 ie 01:27:00 is in fact (or my data) 13:27:00 and the same for any other time value over 12:59.
Again, thanks for any help.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TimeTest](
[Race_Time] [time](0) NULL
) ON [PRIMARY]
GO
insert into timetest
(race_time)
values ('01:27:00')
insert into timetest
(race_time)
values ('02:02:00')
insert into timetest
(race_time)
values ('02:37:00')
insert into timetest
(race_time)
values ('06:00:00')
insert into timetest
(race_time)
values ('12:52:00')convert into 24 hr format
But 12:52:00 is not earlier than 01:27:00. 00:52:00 is the AM time, not 12:52:00.
You are right, just the confusion this has caused me surfacing in my question.
I cant figure out how to cast the selected times to 24hr if you could help be very grateful. Thanks
hope this helps
Use CONVERT. SQL stores time(0) internally as number of seconds past midnight, thus there's no AM/PM in the internal value.
SELECT CONVERT(varchar(8), race_time, 8) AS race_time_24hhmmss
No, just can't get it, struggling. Running the below query on the table and data provided above does not produce a time in 24hr format.
Select CONVERT(VARCHAR(8),(RACE_TIME), 8) 'hh:mi:ss'
from TimeTest
What am I missing? Thank you.
Then RACE_TIME must not be of type "time(0)".
Run as "EXEC sys.sp_help TimeTest" and look at some of the values in the RACE_TIME column directly.
This is what EXEC sys.sp_help todaysraces returned
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
RACE_TIME time no 3 8 0 yes (n/a) (n/a) NULL
and the scale is coming back as n/a but in the table designer it is showing as (time(0), null)
definitely sailing uncharted waters now. Thank you for your time and trouble.
So what's the exact output that you're getting that looks "wrong"?
That SELECT worked fine on your sample data above:
SELECT CONVERT(varchar(8), Race_Time, 8)
FROM dbo.TimeTest
Select CONVERT(VARCHAR(8),(RACE_TIME), 8) 'hh:mi:ss'
from TimeTest
order by Race_Time
hh:mi:ss
01:27:00
02:02:00
02:37:00
06:00:00
12:52:00
I was hoping, expecting, that the hours would be in the 24hr format, ie 1:27 would be displayed as 13:27 and that such would apply to every hour. The first race_time of the day, as per the query is 1:27, whereas in fact the first race_time should be 12:52.
But 1:27 is AM. So if you enter '01:27:00', it stays that.
If you want PM, you have to enter '13:27:00' or '01:27:00PM'.
There is no real issue here, you just have to be careful about the format you use to enter data into the data.
Hi, thanks for all your time and help.
Handle the date properly.