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

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

0	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

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

0	--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

--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

Latest Posts