CODEWITHSUNDEEP
sqlsql-serverstored-proceduressmalldatetime
user2994144
user2994144

Reputation: 167

How to compare smalldatetime in stored procedure

I'm writing stored procedure to compare dates but it's not working properly. How can I make it so it compares only the dates but not the time? What I'm trying to do is compare the times and if the Id is null than insert a new entry with the same name but new time. I'm keeping multiple entries with same name but different test time. 

ALTER PROCEDURE [dbo].[UL_TestData]
(
@Name varchar(30),  
@Test_Time smalldatetime,   
@ID INT output
)
AS
Declare  @UpdateTime smalldatetime
SELECT @ID=ID FROM Info_User WHERE Name=@Name AND  UpdateTime= @Test_Time
IF(@ID IS NULL)
 BEGIN
INSERT INTO Info_User (Name, UpdateTime) VALUES (@Name, @UpdateTime)    
 END

Upvotes: 0

Views: 1638

Answers (2)

alzaimar
alzaimar

Reputation: 4622

I would cast the dates to a plain date which makes this solution independent of implementation details

select @ID=ID 
  from info_user
 where Name = @Name
   and cast (UpdateTime as Date) = Cast(@TestTime as Date)

However, I would either add the date part of the UpdateTime as an additional (calculated) column or split the information into a date and a time part. This makes it much easier to query entries by the plain date.

As a rule of thumb: The type of columns (in general: the table layout) greatly depends on the type of query you usually run against your data.

Edit: As attila pointed out, the date datatype only exists in version 2008 and up

Upvotes: 0

attila
attila

Reputation: 2229

there are a lot of solutions to this depending on what type of DBMS, however here is one:

SELECT @ID=ID FROM Info_User WHERE Name=@Name AND floor(cast(@UpdateTime as float))= floor(cast(@Test_Time as float))

this works because smalldatetime's date is stored a whole numbers, where the time is stored as decimals.

Upvotes: 1

Related Questions