Avoid 1/1/1900 during insert of time only data ?

--STORED PROCEDURE CALL
EXECUTE sp_CREATE_SESSION 92,448,1,3436,1000000,'Mon','10:00 am','1/5/2004','12/27/2004',1,'123',0

Note the '10:00 am" in the call.
This is placing 1/1/1900 10:00:00 AM in the table
It must not place 1/1/1900 in the table

Any sugestions on how to stop it from assuming 1/1/1900?
An After insert trigger is ok if it is the only way.

Here is the script for the table and SP

--TABLE:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblSessionsStuds_tblSessions]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSessions
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblSessionsStuds_tblSessions1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSessions1
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSessions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSessions]
GO

CREATE TABLE [dbo].[tblSessions] (
      [SessionID] [int] IDENTITY (1, 1) NOT NULL ,
      [SeasonID] [int] NULL ,
      [PoolID] [int] NULL ,
      [StationID] [int] NOT NULL ,
      [InstructorID] [int] NULL ,
      [InstrTempID] [int] NULL ,
      [DayCode] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ClassDays] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [TimeCls] [datetime] NULL ,
      [OriginalStartDate] [smalldatetime] NULL ,
      [OriginalEndDate] [smalldatetime] NULL ,
      [MthlyWkly] [smallint] NULL ,
      [MaxNumStuds] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Hold] [bit] NOT NULL ,
      [SessionChanged] [datetime] NULL ,
      [LockID] [int] NULL
) ON [PRIMARY]
GO
GRANT INSERT, UPDATE, DELETE, SELECT ON tblSessions TO PUBLIC
GO




--STORED PROCEDURE:
CREATE PROCEDURE dbo.sp_CREATE_SESSION
      @SeasonID int,
      @PoolID int,
      @StationID int,
      @InstructorID int,
      @DayCode varchar(255),
      @ClassDays varchar(255),
      @TimeCls datetime,
      @OriginalStartDate datetime,
      @OriginalEndDate datetime,
      @MthlyWkly int,
      @MaxNumStuds varchar(25),
      @Hold bit
AS
INSERT INTO tblSessions
      (
      SeasonID,
      PoolID,
      StationID,
      InstructorID,
      DayCode,
      ClassDays,
      TimeCls,
      OriginalStartDate,
      OriginalEndDate,
      MthlyWkly,
      MaxNumStuds,
      Hold
      )
VALUES
      (
      @SeasonID,
      @PoolID,
      @StationID,
      @InstructorID,
      @DayCode,
      @ClassDays,
      @TimeCls,
      @OriginalStartDate,
      @OriginalEndDate,
      @MthlyWkly,
      @MaxNumStuds,
      @Hold
      )
SELECT @@IDENTITY AS SESSION_ID
GO
GRANT EXECUTE ON sp_CREATE_SESSION TO PUBLIC
GO
LVL 2
thorkylAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
from SQL Server Books On-line:

There are no separate time and date data types for storing only times or only dates. If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).

Thus there is no way to avoid the storing of 1/1/1900 as a DATE in the field that you want to hols ONLY the time.  The easy solution would be to change the Time field to a varchar(8) and store the text.  Since you are not going to do any calculations on the time, it is simply for display purposes, that resolves the problem.  If you MUST keep the field as a DATETIME, then you can fomat the output to show only the TIME part when the values are displayed.

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thorkylAuthor Commented:
0
thorkylAuthor Commented:
AW

I am going to award you the points as you did respond and you do have a good point.

Do check the above link as this did solve my problem.

Thorkyl
0
wsteegmansCommented:
Indeed, ...

SQL Server has a smalldatetime and a datetime datatypes, in BOTH you get the DATE and the TIME. Unfortunately, no choice in the matter.

You can insert just a date and you can insert just a time, but SQL will append the rest as it needs to - like 0:00:00 or 1/1/1900 (this is the start of time for SQL server).

What you can do: Insert the time, but use DATEPART or CONVERT to extract just the time back out again.

More info and workarrounds? Read this article - Why doesn't SQL Server allow me to separate DATE and TIME?
http://www.aspfaq.com/show.asp?id=2206

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.