Convert int to time in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert int to time in stored procedure

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2006-11-06 : 13:25:33

I can't seem to make this SP work.

What it should is the following: I am receiving for doctor (Me_Id) a date (Ho_Fecha) and hour (Ho_Hour) and a time (Ho_Time). First I want to check if for the given doc, date and time an entry exists in my table. The time is stored in a datetime field and it appears as 15:00:00


CREATE PROCEDURE sp_CreateHorasMedico
 ( @Me_Id int,
   @Ho_Fecha datetime,
   @Ho_Hour int,
   @Ho_Minute int
 )
 As

declare @tmpDateTime varchar(30)
set @tmpDateTime = cast('1899-12-30 ' & @Ho_Hour & ':' & @Ho_Minute & ':00' as varchar(30))

	/*WHERE     (Ho_Hora = CONVERT(DATETIME, '1899-12-30 08:30:00', 102)) */
	if not exists (SELECT  *  FROM tblHoras  WHERE Ho_Me_Id= @Me_Id AND Ho_Hora= CONVERT(DATETIME, @tmpDateTime, 102) AND datepart( year, Ho_Fecha) = datepart( year, @Ho_Fecha) and datepart( month,  Ho_Fecha ) = datepart( month, @Ho_Fecha) and datepart( day,  Ho_Fecha ) = datepart( day, @Ho_Fecha) )
	begin
		 INSERT INTO tblHoras ( Ho_Me_Id, Ho_Hora,Ho_Fecha)  VALUES (@Me_Id, @tmpDateTime, @Ho_Fecha)
		Declare @Ho_Id int
		SELECT @Ho_Id = SCOPE_IDENTITY()
		RETURN @Ho_Id		
	end
GO

jean-luc
www.corobori.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 13:39:12

1899-12-30 is for ACCESS dates only. SQL Server has 1900-01-01 as date Zero.

You also have to supply information about table tblHoras.

Peter Larsson
Helsingborg, Sweden

Corobori
Posting Yak Master

105 Posts

Posted - 2006-11-06 : 13:43:03

[code]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblHoras]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblHoras]
GO

CREATE TABLE [dbo].[tblHoras] (
[Ho_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Ho_Me_Id] [int] NOT NULL ,
[Ho_Fecha] [datetime] NOT NULL ,
[Ho_Hora] [datetime] NOT NULL ,
[Ho_Pa_Id] [int] NULL ,
[Ho_InputDate] [datetime] NOT NULL ,
[Ho_Estado] [int] NOT NULL ,
[Ho_Tipo] [int] NULL ,
[Ho_Correo] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblHoras] WITH NOCHECK ADD
CONSTRAINT [PK_tblHoras] PRIMARY KEY CLUSTERED
(
[Ho_Id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblHoras] ADD
CONSTRAINT [DF_tblHoras_Ho_InputDate] DEFAULT (getdate()) FOR [Ho_InputDate],
CONSTRAINT [DF_tblHoras_Ho_Estado] DEFAULT (1) FOR [Ho_Estado]
GO

[/code]

jean-luc
www.corobori.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 13:49:36

Why do you separate the date and time information for the appointments into two columns, Ho_Fecha and Ho_Hora?
Has Ho_Fecha ONLY yyyy-mm-dd information and Ho_Hora ONLY 1899-12-30 15:00?

Peter Larsson
Helsingborg, Sweden

Corobori
Posting Yak Master

105 Posts

Posted - 2006-11-06 : 15:46:39

quote:
Originally posted by Peso

Why do you separate the date and time information for the appointments into two columns, Ho_Fecha and Ho_Hora?
Has Ho_Fecha ONLY yyyy-mm-dd information and Ho_Hora ONLY 1899-12-30 15:00?

I can't remember where why it was done that way.

Still I managed to get it working:

CREATE PROCEDURE sp_CreateHorasMedico
 ( @Me_Id int,
   @Ho_Fecha datetime,
   @Ho_Hour int,
   @Ho_Minute int
 )
 As

declare @tmpDateTime datetime
declare @tmp char(20)

set @tmp = cast('1899-12-30' as varchar(10)) + ' ' +  cast(@Ho_Hour as varchar(3)) + ':' +  cast(@Ho_Minute  as varchar(3))
set @tmpDateTime = cast(@tmp as datetime )

	/*WHERE     (Ho_Hora = CONVERT(DATETIME, '1899-12-30 08:30:00', 102)) */
	if not exists (SELECT  *  FROM tblHoras  WHERE Ho_Me_Id= @Me_Id AND datepart( hh, Ho_Hora) =@Ho_Hour  AND  datepart( mi, Ho_Hora) =@Ho_Minute  AND datepart( year, Ho_Fecha) = datepart( year, @Ho_Fecha) and datepart( month,  Ho_Fecha ) = datepart( month, @Ho_Fecha) and datepart( day,  Ho_Fecha ) = datepart( day, @Ho_Fecha) )
	begin
		 INSERT INTO tblHoras ( Ho_Me_Id, Ho_Hora,Ho_Fecha)  VALUES (@Me_Id, @tmpDateTime, @Ho_Fecha)
		Declare @Ho_Id int
		SELECT @Ho_Id = SCOPE_IDENTITY()
		RETURN @Ho_Id		
	end
GO

jean-luc
www.corobori.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources