Inserting two different TIME in single column with AM/PM format in SQL Server

Question

I have following table

 E(E_id(pk),
    E_date (datetime),
    E_time (datetime)
   ,E_Title (varchar (50))

I am trying to insert DATE and TIME in the above Table using following code:

INSERT INTO E(E_Date,E_Title,E_Time)
VALUES('12/10/2017','Every day is a Big day','11:00 am-13:00 Pm')

I executed above code, but SQL is giving me an ERROR Saying Conversion failed when converting date and/or time from character string. I would like to insert the DATE and TIME in the above format.

Any help is appreciated! Thanks in advance!

Answer 1

SQL Server's datetime type is meant to store a "point in time", i.e. one day and one time.

Quote from the link:

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Your code has multiple problems:

1. The string 11:00 am-13:00 Pm is not a valid datetime value, that's why your query is failing.

2. As the datetime type contains a time as well, so '12/10/2017' will actually be saved as 12/10/2017 00:00.

---

So I guess your current table design is not what you actually want.

There's no type in SQL which can hold a "timespan" like 11am-13pm.

As a datetime is actually a "point in time", it's better to use two datetime columns, one for the start and one for the end of your timespan.

When you insert into the table, you specify both date and time for both columns.

Then, you can use (for example) the DATEDIFF function to calculate the timespan between the two datetimes.

Here's a complete example:

CREATE TABLE [dbo].[E](
    [E_id] [int] IDENTITY(1,1) NOT NULL,
    [E_start] [datetime] NULL,
    [E_end] [datetime] NULL
) ON [PRIMARY]
GO

insert into E (E_start, E_end)
values
('2017-12-10T11:00:00', '2017-12-10T13:00:00'),  -- starts and ends on the same day
('2017-12-10T09:00:00', '2017-12-11T10:00:00')   -- starts and ends on different days

select *, DATEDIFF(hh, e_start, e_end) as HoursPassed from e

Result of the last query:

E_id        E_start                 E_end                   HoursPassed
----------- ----------------------- ----------------------- -----------
1           2017-12-10 11:00:00.000 2017-12-10 13:00:00.000 2
2           2017-12-10 09:00:00.000 2017-12-11 10:00:00.000 25

(2 rows affected)

---

To write a query which shows the time as 11:00, you can use CONVERT if you want just the time:

select convert(char(5), e_start, 108) from E

...or FORMAT:

select format(e_start, N'hh\:mm') from E