Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-11-12
4
Medium Priority
?
4,313 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 375 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

670 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