Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 83
help restore my wordpress site made on WAMP 8 29
Postgresql Database remote connection 6 6
MS SQL GROUP BY 5 8
Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…

820 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