thorkyl
asked on
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_tblS essionsStu ds_tblSess ions]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSes sions
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblS essionsStu ds_tblSess ions1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSes sions1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSess ions]') 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
EXECUTE sp_CREATE_SESSION 92,448,1,3436,1000000,'Mon
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_tblS
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSes
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblS
ALTER TABLE [dbo].[tblSessionsStuds] DROP CONSTRAINT FK_tblSessionsStuds_tblSes
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSess
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_
[ClassDays] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_
[TimeCls] [datetime] NULL ,
[OriginalStartDate] [smalldatetime] NULL ,
[OriginalEndDate] [smalldatetime] NULL ,
[MthlyWkly] [smallint] NULL ,
[MaxNumStuds] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_
[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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
http://www.databasejournal.com/features/mssql/article.php/1494281
This solved the problem.