Solved

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

Posted on 2003-11-12
4
4,288 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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