Solved

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

Posted on 2003-11-12
4
4,302 Views
Last Modified: 2007-12-19
--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
0
Comment
Question by:thorkyl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 125 total points
ID: 9732668
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
 
LVL 2

Author Comment

by:thorkyl
ID: 9732670
0
 
LVL 2

Author Comment

by:thorkyl
ID: 9732688
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
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9732694
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question