How to insert 24:00:00 into a datetime field?

Question

I want to insert 24:00:00 into a datetime field as opposed to the 00:00:00 value that is stored.

 

How do I do this?

Answer 1

You can only do this as a string; this is not a valid time component for a date/time field. 

The short answer is:

You can't.

Formatted String Example:

declare @aDate as datetime set @aDate = '2011-07-01'

select
-- @aDate,
 convert(varchar(10), @aDate, 101) + ' 24:00:00'
  as Formatted_Date

/* -------- Output: --------
Formatted_Date
-------------------
07/01/2011 24:00:00
*/

.

Answer 2

Hi, it's not possible to use 24:00:00 in datetime.

---

Best regards

Answer 3

Folks,

That's really unfortunate:

 

Here's my problem:

My time-slice look-up table is set up as follows:

 

ts_id#  | start_ts | end_ts

1 | 23:00 | 23:30

2 | 23:30 | 0:00

3 | 0:00 | 0:30

 

 

and so on ... 

 

I am given a time-slice value and i need to look up the corresponding ts_id#...

It would be really nice if I can store the end_ts value as 24:00:00 as opposed to 00:00:00.

 

For the look-up, "where clause temp_ts_val is between start_ts and end_ts." will fail for ts_id#=2 above; any skillful ideas...

Phillip

Answer 4

Just use a case statement to test for both start_Ts and End_Ts and when 23:30 and 0:00 then display 24:00.

Therefore, instead of selecting end_Ts for your third column, select instead:

case when start_Ts <> '23:30' or end_Ts <> '0:00' then end_Ts
else '24:00' end as end_Ts

Answer 5

Try to use 23:59:59

---

Best regards

Answer 6

Read your ISO Standards for SQL and for temporal data. There is no 24:00:00 Hrs. And columns are not fields. The convention has always been  that 24:00:00 is rounded to 00:00:00 of the next day. Some SQL products will allow 24:00:00 as input and then correct it when the value is displayed.

Do you know about the half-open interval ISO model of time?

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

No sir,  i don't know about the half-open interval of the ISO model of time?

 

Answer 8

This is not a problem. Look at the half-open interval model

CREATE TABLE TimeSlots
(start_time TIME(2) NOT NULL,
 end_time TIME(2) NOT NULL,
 CHECK (start_time < end_time),
 PRIMARY KEY (start_time, end_time));

INSERT INTO TimeSlots
VALUES  ('00:00:00.00', '01:00:00.00'),
        etc
        ('23:00:00.00', '23:59:59.99');


Now make sure that the @in_my_time values are TIME(0), so you can write

 "WHERE @in_my_time BETWEEN start_time AND end_time"

 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL