How can I save time only in a datetime field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can I save time only in a datetime field?

Author

Topic

frankhzhang
Starting Member

1 Post

Posted - 2004-06-18 : 02:47:01

Hi All,

I am not a SQL expert and run into a issue. Could someone help me out?

I am trying to insert some records into a table in SQL 2000. Two of my fields are DateTime type. I am using a stored procedure for this. However, when I try insert a time only value, say "8:00 AM", the value of "1/1/1900 8:00:00 AM" is stored instead.In SQL Server Enterprise Manager, though, I can remove the data portion and just keep the "8:00:00 AM".

How can I just insert the time in this field???

Thanks

Frank

The following are my sproc.

CREATE PROCEDURE [dbo].[tblAvailTimesInsert] (
@pk_TimeID int output,
@pk_DateID int,
@dtmStart nvarchar(12),
@dtmEnd nvarchar(12),
@chkGroup int,
@chkER int
)

AS

SET NOCOUNT ON

INSERT INTO [tblAvailTimes] (
[pk_DateID],
[dtmStart],
[dtmEnd],
[chkGroup],
[chkER]
) VALUES (
@pk_DateID,
@dtmStart,
@dtmEnd,
@chkGroup,
@chkER
)

SET @pk_TimeID = SCOPE_IDENTITY()
GO

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-18 : 03:02:06

If the datatype of dmtStart and dmtEnd is datetime, SQL is going to assign a default date. You would have to change the column to nvarchar. If @dmtStart contains a full date, you would have to use convert(varchar,convert(datetime,@dmtStart),114) instead of @dmtStart to get only the time.

There is no such thing as a time only data type in SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources