How to insert time with AM,PM in my Table?

Posted by Raghuldrag under Sql Server on 12/5/2013 | Points: 10 | Views : 1610 | Status : [Member] | Replies : 6
Hi Friends,



My Table strct:
===========


create table show_bus
(
id int identity,
from_p varchar(100),
to_p varchar(100),
timin time
)


My Expecting OUTPUT:
================

insert into show_bus values('a','b','6:05 AM')


when i tried to enter these format its showing error on sql server 2000


kindly help to me




Responses

Posted by: vishalneeraj-24503 on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refine your table structure as

create table show_bus
(
id int identity,
from_p varchar(100),
to_p varchar(100),
timin varchar(10)
)
Alter time datatype with varchar then it will insert.

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raghuldrag on: 12/5/2013 [Member] Starter | Points: 25

Up
0
Down
thnx Vishal@Neeraj my friend

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Please mark it if the solution helps you.

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raghuldrag on: 12/5/2013 [Member] Starter | Points: 25

Up
0
Down
ya its help full to my expectation

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
For presentation purpose only AM, PM is required...
Better to use proper data types for storing datetime values...

If you use VARCHAR(10) it may cause problem in future. Any one can enter different formats of time values such as 01:00 AM, 01.11 Am, 1 AM, 1.00 AM
For suppose if you wish to get date and time from two fields DATEColumn and TimeColumn
you will definitely get errors in future...


if you use DATETIME type to store time values....

--sample code
DECLARE @foo TABLE
(
dt DATETIME
)


INSERT @foo(dt) VALUES('20011031')
INSERT @foo(dt) VALUES('3:25 PM')

SELECT RIGHT(CONVERT(VARCHAR(20), dt, 100), 7), dt FROM @foo

--Results
-------
12:00AM
3:25PM


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 12/5/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--sample code with your table
create table show_bus 
(
id int identity,
from_p varchar(100),
to_p varchar(100),
timin DATETIME
)


--My Expecting OUTPUT:
--================

insert into show_bus values('a','b','6:05 AM')

SELECT id, from_p, to_p, RIGHT(CONVERT(VARCHAR(20), timin, 100), 7) TimeFormat FROM show_bus

--Results
-------
id from_p to_p TimeFormat
1 a b 6:05AM





Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response